Java Code Examples for com.mysql.jdbc.PreparedStatement

The following examples show how to use com.mysql.jdbc.PreparedStatement. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: StudentManagement   Source File: CourseDao.java    License: MIT License 6 votes vote down vote up
public ArrayList<Course> query_all_course() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from course order by cno;";
	ArrayList<Course> results = new ArrayList<Course>();
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Course temp = new Course();
			temp.setCno(rs.getString("Cno"));
			temp.setCname(rs.getString("Cname"));
			temp.setCteacher(rs.getString("Cteacher"));
			temp.setCcredit(rs.getInt("Ccredit"));
			results.add(temp);
		}
		// 关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 
Example 2
Source Project: StudentManagement   Source File: CourseDao.java    License: MIT License 6 votes vote down vote up
public int insert_course(String Cno, String Cname, String Cteacher, double Ccredit) {
	Connection conn = DBUtils.getConnection();
	String sql = "insert into course values(?,?,?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Cno);
		ps.setString(2, Cname);
		ps.setString(3, Cteacher);
		ps.setDouble(4, Ccredit);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 3
Source Project: StudentManagement   Source File: CourseDao.java    License: MIT License 6 votes vote down vote up
public int delete_course(String Cno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from course where Cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 4
Source Project: StudentManagement   Source File: CourseDao.java    License: MIT License 6 votes vote down vote up
public int alter_course(String cno,String after_cno,String after_cname,String after_cteacher,double after_ccredit) {
	Connection conn = DBUtils.getConnection();
	String sql = "update course set cno = ?,cname = ?,cteacher = ?,ccredit = ? where cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_cno);
		ps.setString(2, after_cname);
		ps.setString(3, after_cteacher);
		ps.setDouble(4, after_ccredit);
		ps.setString(5, cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 5
Source Project: StudentManagement   Source File: UserDao.java    License: MIT License 6 votes vote down vote up
public boolean userIsExist(String username) {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from user where username = ?";
	try{
		//获取PreparedStatement对象
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, username);//给用户对象属性赋值
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			//数据库中存在此用户
			return true;
		}
		//释放资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return false;
}
 
Example 6
Source Project: StudentManagement   Source File: UserDao.java    License: MIT License 6 votes vote down vote up
public ArrayList<User> query_all_user() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from user order by username;";
	ArrayList<User> results = new ArrayList<User>();
	
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		while(rs.next()){
			User temp = new User();
			temp.setUsername(rs.getString("username"));
			temp.setPassword(rs.getString("password"));
			temp.setLevel(rs.getString("level"));
			results.add(temp);
		}
		//关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 
Example 7
Source Project: StudentManagement   Source File: UserDao.java    License: MIT License 6 votes vote down vote up
public int insert_user(String username,String password,String level){
	Connection conn = DBUtils.getConnection();
	String sql = "insert into user values(?,?,?);";
	
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		
		ps.setString(1, username);
		ps.setString(2, password);
		ps.setString(3, level);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 8
Source Project: StudentManagement   Source File: UserDao.java    License: MIT License 6 votes vote down vote up
public int delete_user(String username) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from user where username = ?;";
	
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, username);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 9
Source Project: StudentManagement   Source File: UserDao.java    License: MIT License 6 votes vote down vote up
public int alter_user(String username,String after_username,String after_password,String after_level) {
	Connection conn = DBUtils.getConnection();
	String sql = "update user set username = ?,password = ?,level = ? where username = ?;";
	
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_username);
		ps.setString(2, after_password);
		ps.setString(3, after_level);
		ps.setString(4, username);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 10
Source Project: StudentManagement   Source File: DepartmentDao.java    License: MIT License 6 votes vote down vote up
public ArrayList<Department> query_all_department() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from department order by dno;";
	ArrayList<Department> results = new ArrayList<Department>();
	
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			Department temp = new Department();
			temp.setDno(rs.getString("Dno")); 
			temp.setDname(rs.getString("Dname"));
			results.add(temp);
		}
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 
Example 11
Source Project: StudentManagement   Source File: DepartmentDao.java    License: MIT License 6 votes vote down vote up
public int insert_department(String dno,String dname){
	Connection conn = DBUtils.getConnection();
	String sql = "insert into department values(?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, dno);
		ps.setString(2, dname);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 12
Source Project: StudentManagement   Source File: DepartmentDao.java    License: MIT License 6 votes vote down vote up
public int delete_department(String dno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from department where dno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, dno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 13
Source Project: StudentManagement   Source File: DepartmentDao.java    License: MIT License 6 votes vote down vote up
public int alter_department(String dno,String after_dno,String after_dname) {
	Connection conn = DBUtils.getConnection();
	String sql = "update department set dno = ?,dname = ? where dno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_dno);
		ps.setString(2, after_dname);
		ps.setString(3, dno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 14
Source Project: StudentManagement   Source File: ClassDao.java    License: MIT License 6 votes vote down vote up
public ArrayList<Class> query_all_class() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from class order by clno;";
	ArrayList<Class> results = new ArrayList<Class>();
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Class temp = new Class();
			temp.setClno(rs.getString("clno"));
			temp.setClname(rs.getString("clname"));
			temp.setDno(rs.getString("dno"));
			results.add(temp);
		}
		// 关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 
Example 15
Source Project: StudentManagement   Source File: ClassDao.java    License: MIT License 6 votes vote down vote up
public int insert_class(String clno, String clname, String dno) {
	Connection conn = DBUtils.getConnection();
	String sql = "insert into class values(?,?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, clno);
		ps.setString(2, clname);
		ps.setString(3, dno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 16
Source Project: StudentManagement   Source File: ClassDao.java    License: MIT License 6 votes vote down vote up
public int delete_class(String clno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from class where clno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, clno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 17
Source Project: StudentManagement   Source File: ClassDao.java    License: MIT License 6 votes vote down vote up
public int alter_class(String clno, String after_clno, String after_clname, String after_dno) {
	Connection conn = DBUtils.getConnection();
	String sql = "update class set clno = ?,clname = ?,dno = ? where clno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_clno);
		ps.setString(2, after_clname);
		ps.setString(3, after_dno);
		ps.setString(4, clno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 18
Source Project: StudentManagement   Source File: SCDao.java    License: MIT License 6 votes vote down vote up
public int insert_sc(String Sno, String Cno, double Grade) {
	Connection conn = DBUtils.getConnection();
	String sql = "insert into sc values(?,?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Sno);
		ps.setString(2, Cno);
		ps.setDouble(3, Grade);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 19
Source Project: StudentManagement   Source File: SCDao.java    License: MIT License 6 votes vote down vote up
public int delete_sc(String Sno,String Cno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from sc where sno = ? and cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Sno);
		ps.setString(2, Cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 20
Source Project: StudentManagement   Source File: SCDao.java    License: MIT License 6 votes vote down vote up
public int alter_sc(String Sno, String Cno,double after_grade) {
	Connection conn = DBUtils.getConnection();
	String sql = "update sc set grade = ? where sno = ? and cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setDouble(1, after_grade);
		ps.setString(2, Sno);
		ps.setString(3, Cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 21
Source Project: StudentManagement   Source File: StudentDao.java    License: MIT License 6 votes vote down vote up
public ArrayList<Student> query_all_student() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from student order by sno;";
	ArrayList<Student> results = new ArrayList<Student>();
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Student temp = new Student();
			temp.setSno(rs.getString("sno"));
			temp.setSname(rs.getString("sname"));
			temp.setSsex(rs.getString("ssex"));
			temp.setSage(rs.getInt("sage"));
			temp.setClno(rs.getString("clno"));
			results.add(temp);
		}
		// 关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 
Example 22
Source Project: StudentManagement   Source File: StudentDao.java    License: MIT License 6 votes vote down vote up
public int insert_student(String Sno,String Sname,String Ssex,int Sage,String Clno) {
	Connection conn = DBUtils.getConnection();
	String sql = "insert into student values(?,?,?,?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Sno);
		ps.setString(2, Sname);
		ps.setString(3, Ssex);
		ps.setInt(4, Sage);
		ps.setString(5, Clno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 23
Source Project: StudentManagement   Source File: StudentDao.java    License: MIT License 6 votes vote down vote up
public int delete_student(String sno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from student where sno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, sno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 24
Source Project: StudentManagement   Source File: StudentDao.java    License: MIT License 6 votes vote down vote up
public int alter_class(String sno, String after_sno,String after_sname,String after_ssex,int after_sage,String after_clno) {
	Connection conn = DBUtils.getConnection();
	String sql = "update student set sno = ?,sname = ?,ssex = ?,sage = ?,clno = ? where sno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_sno);
		ps.setString(2, after_sname);
		ps.setString(3, after_ssex);
		ps.setInt(4, after_sage);
		ps.setString(5, after_clno);
		ps.setString(6, sno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 
Example 25
Source Project: bird-java   Source File: DruidTraceSQLFilter.java    License: MIT License 6 votes vote down vote up
/**
 * SQL语句执行前操作
 *
 * @param statement statement
 */
private void beforeStatementExecute(StatementProxy statement) {
    if (!this.isSupport(statement.getLastExecuteSql())) {
        return;
    }

    Statement rawObject = statement.getRawObject();
    String sql;
    String database = null;
    try {
        if (mysql5Available && rawObject instanceof PreparedStatement) {
            sql = ((PreparedStatement) rawObject).asSql();
        } else {
            sql = rawObject.toString();
        }
        database = statement.getConnection().getCatalog();
    } catch (SQLException e) {
        sql = rawObject.toString();
        log.error(e.getSQLState(), e);
    }
    TraceSQL traceSQL = new TraceSQL(database, sql);
    traceSQL.setStart(System.currentTimeMillis());

    statement.putAttribute(ATTRIBUTE_KEY, traceSQL);
}
 
Example 26
Source Project: r-course   Source File: UtilsTest.java    License: MIT License 6 votes vote down vote up
/**
 * Tests Util.isJdbcInterface()
 * 
 * @throws Exception
 */
public void testIsJdbcInterface() throws Exception {
    // Classes directly or indirectly implementing JDBC interfaces.
    assertTrue(Util.isJdbcInterface(PreparedStatement.class));
    assertTrue(Util.isJdbcInterface(StatementImpl.class));
    assertTrue(Util.isJdbcInterface(Statement.class));
    assertTrue(Util.isJdbcInterface(ResultSetImpl.class));
    Statement s = (Statement) Proxy.newProxyInstance(this.getClass().getClassLoader(), new Class<?>[] { Statement.class }, new InvocationHandler() {
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            return null;
        }
    });
    assertTrue(Util.isJdbcInterface(s.getClass()));

    // Classes not implementing JDBC interfaces.
    assertFalse(Util.isJdbcInterface(Util.class));
    assertFalse(Util.isJdbcInterface(UtilsTest.class));

}
 
Example 27
Source Project: Komondor   Source File: UtilsTest.java    License: GNU General Public License v3.0 6 votes vote down vote up
/**
 * Tests Util.isJdbcInterface()
 * 
 * @throws Exception
 */
public void testIsJdbcInterface() throws Exception {
    // Classes directly or indirectly implementing JDBC interfaces.
    assertTrue(Util.isJdbcInterface(PreparedStatement.class));
    assertTrue(Util.isJdbcInterface(StatementImpl.class));
    assertTrue(Util.isJdbcInterface(Statement.class));
    assertTrue(Util.isJdbcInterface(ResultSetImpl.class));
    Statement s = (Statement) Proxy.newProxyInstance(this.getClass().getClassLoader(), new Class<?>[] { Statement.class }, new InvocationHandler() {
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            return null;
        }
    });
    assertTrue(Util.isJdbcInterface(s.getClass()));

    // Classes not implementing JDBC interfaces.
    assertFalse(Util.isJdbcInterface(Util.class));
    assertFalse(Util.isJdbcInterface(UtilsTest.class));

}
 
Example 28
Source Project: mapleLemon   Source File: AdminCommand.java    License: GNU General Public License v2.0 6 votes vote down vote up
@Override
public int execute(MapleClient c, String[] splitted) {
    if (splitted.length < 2) {
        c.getPlayer().dropMessage(0, splitted[0] + " <SQL命令>");
        return 0;
    }
    try {
        Connection con = (Connection) DatabaseConnection.getConnection();
        PreparedStatement ps = (PreparedStatement) con.prepareStatement(StringUtil.joinStringFrom(splitted, 1));
        ps.executeUpdate();
        ps.close();
    } catch (SQLException e) {
        c.getPlayer().dropMessage(0, "执行SQL命令失败");
        return 0;
    }
    return 1;
}
 
Example 29
Source Project: brave   Source File: TracingStatementInterceptor.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Uses {@link ThreadLocalSpan} as there's no attribute namespace shared between callbacks, but
 * all callbacks happen on the same thread.
 *
 * <p>Uses {@link ThreadLocalSpan#CURRENT_TRACER} and this interceptor initializes before
 * tracing.
 */
@Override
public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement,
  Connection connection) {
  // Gets the next span (and places it in scope) so code between here and postProcess can read it
  Span span = ThreadLocalSpan.CURRENT_TRACER.next();
  if (span == null || span.isNoop()) return null;

  // When running a prepared statement, sql will be null and we must fetch the sql from the statement itself
  if (interceptedStatement instanceof PreparedStatement) {
    sql = ((PreparedStatement) interceptedStatement).getPreparedSql();
  }
  int spaceIndex = sql.indexOf(' '); // Allow span names of single-word statements like COMMIT
  span.kind(CLIENT).name(spaceIndex == -1 ? sql : sql.substring(0, spaceIndex));
  span.tag("sql.query", sql);
  parseServerIpAndPort(connection, span);
  span.start();
  return null;
}
 
Example 30
Source Project: StudentManagement   Source File: UserDao.java    License: MIT License 5 votes vote down vote up
public User login(String username,String password) {
	Connection conn = DBUtils.getConnection();
	User user = null;
	String sql = "select * from user where username = ? and password = ?;";
	
	try {
		//获取PreparedStatement对象
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		//对sql参数进行动态赋值
		ps.setString(1, username);
		ps.setString(2, password);
		ResultSet rs = ps.executeQuery();//查询结果集
		
		//判断数据库中是否存在该用户
		if(rs.next()){
			user = new User();//实例化一个user对象
			//给用户对象赋值
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			user.setLevel(rs.getString("level"));
		}
		//释放资源
		rs.close();
		ps.close();
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return user;
}