本文共 2982 字，大约阅读时间需要 9 分钟。

jdbc简介

使用时添加数据库驱动到依赖中。

Statement

//注册驱动，mysql5.x不带cj，mysql8.x带cjClass.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/db_mall?serverTimezone=UTC";String user = "chy";String pwd = "abcd";// 获取数据库连接Connection connection = DriverManager.getConnection(url, user, pwd);//通过数据库连接获取Statement对象Statement statement = connection.createStatement();//通过Statement对象执行sql语句String sql = "select * from tb_user";ResultSet resultSet = statement.executeQuery(sql);   //遍历结果集while (resultSet.next()){
       int id = resultSet.getInt("id");  //参数可以是列名或者列索引，列索引从1开始，1表示结果集中第一列    String name = resultSet.getString("name");  //建议使用列名，见名知义    System.out.println(id+"\t"+name);}//关闭资源resultSet.close();connection.close();

int[] ageList = {
   19, 21, 35};String[] nameList={
   "张三","李四","王五"};for(int i=0;i

• Statement没有提供设置sql语句参数的方法，要设置sql语句的参数，需使用字符串拼接，使用+拼接字符串有sql注入的风险，尽量使用concat()拼接字符串。如果参数众多，sql语句很长，可读性差。
• 每次都要重新编译sql语句，浪费时间

使用Statement的子接口PreparedStatement可解决以上问题

PreparedStatement

Class.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/db_mall?serverTimezone=UTC";String user = "chy";String pwd = "abcd";Connection connection = DriverManager.getConnection(url, user, pwd);//使用？占位String sql = "select * from tb_user where id=? and name=?";//预编译，编译后sql语句保存在内存中，以后使用时替换参数即可，效率高PreparedStatement preparedStatement = connection.prepareStatement(sql);//设置参数，第几个？、值preparedStatement.setInt(1,1);preparedStatement.setString(2,"张三");//执行sql语句ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()){
   	//.....}resultSet.close();connection.close();

//批量操作String[] nameList = {
   "张三","李四","王五"};int[] ageList = {
   19, 18, 20};String sql = "insert into tb_user (name,age) values (?,?)";PreparedStatement preparedStatement = connection.prepareStatement(sql);for(int i=0;i

从配置文件加载数据库配置

resources下新建文件mysql.properties，右键标识为Resource Root

driver=com.mysql.cj.jdbc.Driverurl=jdbc:mysql://localhost:3306/db_mall?serverTimezone=UTCuser=chypassword=abcd

//从properties文件加载数据库配置Properties properties = new Properties();FileInputStream inputStream = this.getClass().getResourceAsStream("/mysql.properties");properties.load(inputStream);String driver = properties.getProperty("driver");String url = properties.getProperty("url");String user = properties.getProperty("user");String pwd=properties.getProperty("password");Class.forName(driver);Connection connection = DriverManager.getConnection(url, user, pwd);

jdbc事务管理

Class.forName(driver);Connection connection = DriverManager.getConnection(url, user, pwd);connection.setAutoCommit(false);   //关闭自动提交，开启事务。默认值true//.....  //操作数据库try{
       connection.commit();   //提交事务}catch (SQLException e){
       connection.rollback();  //失败就回滚} finally {
       preparedStatement1.close();    preparedStatement2.close();    connection.close();}

说明

• 有一个dao层的小框架Dbutils，封装了jdbc，可以将结果集简单映射为实体类
• 可以使用连接池c3p0、dbcp、druid

转载地址：http://zwhlb.baihongyu.com/