JDBC访问数据库的一些小技巧

一. 连接

1.使用try with resources关闭JDBC资源

示例代码如下：

public List<User> getUser(int userId) {
    try (Connection con = DriverManager.getConnection(myConnectionURL);
         PreparedStatement ps = createPreparedStatement(con, userId); 
         ResultSet rs = ps.executeQuery()) {

         // process the resultset here, all resources will be cleaned up

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

private PreparedStatement createPreparedStatement(Connection con, int userId) throws SQLException {
    String sql = "SELECT id, username FROM users WHERE id = ?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, userId);
    return ps;
}

摘自：

How should I use try-with-resources with JDBC?

传统的关闭JDBC资源的方法，如下：

public static void realeaseAll(ResultSet rs,Statement stmt,Connection conn){
    if(rs!=null){
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (stmt!=null){
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (conn != null) {
        try{
            conn.close();
        } catch (SQLException e) {
           e.printStackTrace();
        }
   }
}

或者利用jdk1.7 AutoCloseable，代码如下。

/**
* 如需同时关闭resultset,statement与connection，请依次按顺序关闭，即按照r->s->c的顺序将参数放入closeAll
* @param obj
*/
public static void closeAll(AutoCloseable... obj) {
   for (AutoCloseable item : obj) {
       try {
           if(item!=null){
               item.close();
           }
       } catch (Exception e) {
            e.printStackTrace();
       }
   }
}

在jdk1.7中因为所有的资源都要实现AutoCloseable接口，所以可以利用其进行关闭。当然，直接用try with resources语法是最好的。

2.使用C3P0连接数据库提示“Apparent Dead Lock”

一般都是因为用户名密码写错了(注意空格等)。不是这个原因的再查看其它地方。

3.使用C3P0连接数据的工具类JdbcPoolUtil

public class JdbcPoolUtil {
    private static ComboPooledDataSource cpds;
    
    static{
        
        cpds = new ComboPooledDataSource();//默认读取classpath下的c3p0.properties文件
        /*Properties prop = new Properties();//另外一种读取配置文件的方法
        try {
            InputStream in = JdbcPoolUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            prop.load(in);
            cpds.setDriverClass(prop.getProperty("jdbc.driverClass"));
            cpds.setJdbcUrl(prop.getProperty("jdbc.jdbcUrl"));
            cpds.setUser(prop.getProperty("jdbc.user"));
            cpds.setPassword(prop.getProperty("jdbc.password"));
            cpds.setInitialPoolSize(Integer.parseInt(prop.getProperty("jdbc.initialPoolSize")));
            cpds.setMaxPoolSize(Integer.parseInt(prop.getProperty("jdbc.maxPoolSize")));
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        } */
    }
    
    public static DataSource getDataSource() {
        return cpds;
    }
    
    public static Connection getConnection() throws SQLException{
        Connection conn = cpds.getConnection();
        return conn;
    }
    
    //其他代码

}

这里默认读取classpath下的c3p0.properties文件，也可使用注释中的方法对指定配置文件读取。

二. 处理日期

1. 将指定格式的日期字符串转化为LocalDateTime

String timeStamp = "20170818103605"
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
LocalDateTime dateTime = LocalDateTime.parse(timeStamp, formatter);

三. 写入

1. 一种通用的插入方法

使用insert插入数据，一般格式如下：insert table1(col1, col2) values(?, ?)。各种插入业务所不同的是每列的数据类型和到底有多少列。可以使用如下方法进行统一插入：

public static int insert(Connection conn, String sql, Object[] params){
        PreparedStatement pstmt = null;
        int result = 0;
        try{
            pstmt = conn.prepareStatement(sql);
            for(int i =0; i<params.length; i++){
                pstmt.setString(i+1, params[i]!=null?params[i].toString():null);
            }
            result = pstmt.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            //关闭数据库相关资源的代码
        }
        return result;
    }

注意：params中存储的对象的toString方法返回的值是否是你需要插入的值。
还可使用setObject(int parameterIndex, Object x)方法，更简单。不过这两种方法在处理null值的时候有兼容性问题，可使用setNull或 setObject(int parameterIndex, Object x, int sqlType)。

四. 读取

1. 当数据库中某列值NULL是，ResultSet的getDouble(任何get数值类型的操作)结果返回0.0

public class ReadData {
    
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test1";
        String userName = "root";
        String password = "123456";
        String sql = "select id,stuno,age,birthdate,major from students";

        try (Connection con = DriverManager.getConnection(url, userName, password);
                PreparedStatement pStatement = con.prepareStatement(sql);
                ResultSet rs = pStatement.executeQuery()) {
            while (rs.next()) {
                /*注意：getInt的返回值*/
                System.out.printf("id=%s stuno=%s age=%s bd=%s major=%s%n", rs.getInt("id"), rs.getString("stuno"), rs.getInt("age"),
                        rs.getDate("birthdate"), rs.getString("major"));
                /*可以全用getString返回数据*/
                System.out.printf("id=%s stuno=%s age=%s bd=%s major=%s%n", rs.getString("id"), rs.getString("stuno"), rs.getString("age"),
                        rs.getString("birthdate"), rs.getString("major"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }
}

对于记录：2 20150112 吴京 m null null null返回结果如下：

id=2 stuno=20150112 age=0 bd=null major=null
id=2 stuno=20150112 age=null bd=null major=null

可以看到age虽然为Null但是返回0。那么如何返回null值呢？参考如下代码:

Integer age = rs.getInt("age");
Object oAge = rs.getObject("age");
System.out.printf("age=%s, oAge=%s%n",age, oAge);

打印结果为age=0, oAge=null。还可使用如下方法：

Integer age = rs.getInt("age");
if (rs.wasNull())
    age = null;

2. 使用getString可以以字符串形式返回任何类型的数据

数据同上，使用如下代码：

/*可以全用getString以字符串形式返回数据*/
System.out.printf("id=%s stuno=%s age=%s bd=%s major=%s%n", rs.getString("id"), rs.getString("stuno"), rs.getString("age"), rs.getString("birthdate"), rs.getString("major"));

返回结果为：id=2 stuno=20150112 age=null bd=null major=null。注意:数据库中的null值被转化为null。