1.PreparedStatement执行的SQL语句中的参数用问号(?)来表示,调用PreparedStatement对象的setXxx()方法来设置这些参数。setXxx()方法有两个参数,第一个参数是要设置的SQL语句中的参数的索引(从1开始),第二个是设置的SQL语句中的参数的值。调用executeQuery(),返回ResultSet对象。调用executeUpdate():执行更新(增、删、修改)。
添加记录:
public static void main(String[] args) throws Exception { Scanner scanner = new Scanner(System.in); System.out.print("请输入添加管理员的名字:"); String admin_name = scanner.nextLine(); System.out.println("请输入添加管理员的密码:"); String admin_pwd = scanner.nextLine(); Properties properties = new Properties(); properties.load(new FileInputStream("src\\mysql.properties")); String user = properties.getProperty("user"); String password = properties.getProperty("password"); String driver = properties.getProperty("driver"); String url = properties.getProperty("url"); Class.forName(driver); Connection connection = DriverManager.getConnection(url, user, password); //添加记录 String sql = "insert into admin values(?,?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, admin_name); preparedStatement.setString(2, admin_pwd); int rows = preparedStatement.executeUpdate(); System.out.println(rows > 0 ? "执行成功" : "执行失败"); preparedStatement.close(); connection.close(); }
修改记录:
public static void main(String[] args) throws Exception { Scanner scanner = new Scanner(System.in); System.out.print("请输入管理员的名字:"); String admin_name = scanner.nextLine(); System.out.println("请输入管理员的新密码:"); String admin_pwd = scanner.nextLine(); Properties properties = new Properties(); properties.load(new FileInputStream("src\\mysql.properties")); String user = properties.getProperty("user"); String password = properties.getProperty("password"); String driver = properties.getProperty("driver"); String url = properties.getProperty("url"); Class.forName(driver); Connection connection = DriverManager.getConnection(url, user, password); //修改记录 String sql = "update admin set pwd = ? where name = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, admin_pwd); preparedStatement.setString(2, admin_name); int rows = preparedStatement.executeUpdate(); System.out.println(rows > 0 ? "执行成功" : "执行失败"); preparedStatement.close(); connection.close(); }
删除记录:
public static void main(String[] args) throws Exception { Scanner scanner = new Scanner(System.in); System.out.print("请输入删除管理员的名字:"); String admin_name = scanner.nextLine(); Properties properties = new Properties(); properties.load(new FileInputStream("src\\mysql.properties")); String user = properties.getProperty("user"); String password = properties.getProperty("password"); String driver = properties.getProperty("driver"); String url = properties.getProperty("url"); Class.forName(driver); Connection connection = DriverManager.getConnection(url, user, password); //删除记录 String sql = "delete from admin where name = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, admin_name); int rows = preparedStatement.executeUpdate(); System.out.println(rows > 0 ? "执行成功" : "执行失败"); preparedStatement.close(); connection.close(); }
2.Jdbc API:
DriverManager驱动管理类——>getConnection(url,user,pwd)获取到连接。
Connection接口——>createStatement创建Statement对象;preparedStatement(sql)生成预处理对象。
Statement接口——>executeUpdate(sql)执行dml语句,返回影响的行数;executeQuery(sql)执行查询,返回ResultSet对象;execute(sql)执行任意的sql,返回布尔值。
PreparedStatement接口——>executeUpdate()执行dml;executeQuery()执行查询,返回ResultSet;execute()执行任意sql,返回布尔值;setXxx(占位符索引,占位符的值)解决SQL注入;setObject(占位符索引,占位符的值)。
ResultSet(结果集)——>next()向下移动一行,如果没有下一行,返回false();previous()向上移动一行,如果没有上一行,返回false();getXxx(列的索引|列名)返回对应列的值,接收类型是Xxx;getobject(列的索引|列名)返回对应列的值,接收类型为Object。
3.在jdbc操作中,获取连接和释放资源是经常使用到,可以将其封装JDBC连接的工具类JDBCUtils。
封装到一个工具类中:
public class JDBCUtils { private static String user; //用户名 private static String password; //密码 private static String url; //url private static String driver; //驱动名 static { Properties properties = new Properties(); try { properties.load(new FileInputStream("src\\mysql.properties")); user = properties.getProperty("user"); password = properties.getProperty("password"); url = properties.getProperty("url"); driver = properties.getProperty("driver"); } catch (IOException e) { throw new RuntimeException(e); } } public static Connection getConnection() { try { return DriverManager.getConnection(url, user, password); } catch (SQLException e) { throw new RuntimeException(e); } } //关闭相关资源 public static void close(ResultSet set, Statement statement, Connection connection) { try { if (set != null) { set.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { throw new RuntimeException(e); } } }
查询和dml操作:
public static void main(String[] args) { JDBCUtils_Use use = new JDBCUtils_Use(); //use.testDML(); use.testSelect(); } public void testSelect() { Connection connection = null; String sql = "select * from actor where id = ?"; PreparedStatement preparedStatement = null; ResultSet set = null; try { connection = JDBCUtils.getConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, 3); //执行,得到结果集 set = preparedStatement.executeQuery(); //遍历该结果集 while (set.next()) { int id = set.getInt("id"); String name = set.getString("name"); String sex = set.getString("sex"); Date borndate = set.getDate("borndate"); String phone = set.getString("phone"); System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone); } } catch (SQLException e) { e.printStackTrace(); } finally { //关闭资源 JDBCUtils.close(set, preparedStatement, connection); } } public void testDML() { Connection connection = null; String sql = "update actor set name = ? where id = ?"; PreparedStatement preparedStatement = null; try { connection = JDBCUtils.getConnection(); preparedStatement = connection.prepareStatement(sql); //给占位符赋值 preparedStatement.setString(1, "小军"); preparedStatement.setInt(2, 3); preparedStatement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { //关闭资源 JDBCUtils.close(null, preparedStatement, connection); } }