原生jdbc操作

1：加入dbcp连接池依赖

<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-pool2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.4.2</version>
</dependency>

2：jdbc.properties：

#BasicDataSourceFactory.createDataSource 需与连接池中字段保持一致
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=1
#初始连接数
initialSize=5
#最大空闲数
maxIdle=10
#最小空闲数
minIdle=5
#最大连接数量
maxActive=15

3：封装工具类

package com.jxd.Testt.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

/**
* @ClassName JDBCUtil
* @Description jdbc工具类
* @author jxd
* @Date 2018年5月16日 下午5:11:39
* @version 1.0.0
*/
public class JDBCUtil {
private static Properties p;
private static DataSource dataSource;
static { // 加载JDBCUtil类的时候调用
try {
ClassLoader loader = Thread.currentThread().getContextClassLoader();
InputStream in = loader.getResourceAsStream("jdbc.properties");
Properties p = new Properties();
p.load(in);
// 通过工厂类方式创建缓冲池
dataSource = BasicDataSourceFactory.createDataSource(p);
} catch (Exception e) {
e.printStackTrace();
}
}

/**
* @Description (获得连接)
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}

/**
* @Description (释放资源)
* @param conn
* @param state
* @param result
*/
public static void close(Connection conn, Statement state, ResultSet result) {

try {
if (result != null) {
result.close();
}

} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}

} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (state != null) {
state.close();
}

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

}
}

 

4测试：

public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
conn.setAutoCommit(false); // 设为手动提交
ps = conn.prepareStatement("INSERT INTO `test`.`st` (`uid`, `name`) VALUES (?, ?)");
//jdbc 参数下标是从1开始的 hibernate等框架是从0开始的....
ps.setString(1, UUID.randomUUID().toString());
ps.setString(2, String.valueOf(Math.random()));
ps.execute();
conn.commit();// 提交事物放到最后,保证执行异常数据回滚
System.out.println("自定义sql执行成功....................");
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();
System.out.println("发生异常,数据回滚....................");
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} finally {
JDBCUtil.close(conn, ps, rs);
}
}