JDBC和连接池
数据库连接方式 直接用connect05
@Test
public void connect01() throws SQLException {
//1.注册驱动 com.mysql.jdbc.Driver
Driver driver = new com.mysql.jdbc.Driver();
//2.得到连接 jdbc:mysql://
String url ="jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=UTF-8";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","1234");
Connection connect = driver.connect(url, properties);
//3.执行语句
String sql = "insert into actor values(1,'孙悟空','女','1900-10-1','12345')";
Statement statement = connect.createStatement();//发送sql
int i = statement.executeUpdate(sql);//受影响的行数
System.out.printf(i>0 ? "success" :"default");
//4.关闭
statement.close();
connect.close();
}
@Test
//反射加载 ，动态加载更加灵活，减少依赖性
public void connect02() throws Exception{
Class aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver =(Driver) aClass.newInstance();
String url ="jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=UTF-8";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","1234");
Connection connect = driver.connect(url, properties);
//3.执行语句
String sql = "insert into actor values(2,'八戒','男','1901-10-1','12345')";
Statement statement = connect.createStatement();//发送sql
int i = statement.executeUpdate(sql);//受影响的行数
System.out.printf(i>0 ? "success" :"default");
//4.关闭
statement.close();
connect.close();
}
@Test
public void connect03() throws Exception{
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver =(Driver) aClass.newInstance();
String url ="jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=UTF-8";
String user = "root";
String password = "1234";
DriverManager.registerDriver(driver);//注册driver驱动
Connection connection = DriverManager.getConnection(url, user, password);
//3.执行语句
String sql = "insert into actor values(3,'沙僧','女','1900-10-1','12345')";
Statement statement = connection.createStatement();//发送sql
int i = statement.executeUpdate(sql);//受影响的行数
System.out.printf(i>0 ? "success" :"default");
//4.关闭
statement.close();
connection.close();
}
@Test
//class for 自动完成注册驱动 DriverManager
/** 静态代码块 在类加载时会执行一次
* static {
* try {
* DriverManager.registerDriver(new Driver());
* } catch (SQLException var1) {
* throw new RuntimeException("Can't register driver!");
* }
* }
*/
public void connect04() throws Exception{
// Class.forName("com.mysql.jdbc.Driver"); mysql5.1.6以后不需要
// 自动调用驱动下的 libs\mysql-connector-java-5.1.37-bin.jar!\META-INF\services\java.sql.Driver文本中的类名称去注册
String url ="jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=UTF-8";
String user = "root";
String password = "1234";
Connection connection = DriverManager.getConnection(url, user, password);
//3.执行语句
String sql = "insert into actor values(5,'小白龙','女','1900-10-1','12345')";
Statement statement = connection.createStatement();//发送sql
int i = statement.executeUpdate(sql);//受影响的行数
System.out.printf(i>0 ? "success" :"default");
//4.关闭
statement.close();
connection.close();
}
@Test
//使用配置文件
public void connect05() throws Exception{
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
String sql = "insert into actor values(6,'小龙女','女','1900-10-1','12345')";
Statement statement = connection.createStatement();//发送sql
int i = statement.executeUpdate(sql);//受影响的行数
System.out.printf(i>0 ? "success" :"default");
//4.关闭
statement.close();
connection.close();
}
ResultSet
String sql ="select * from actor";
Class.forName(driver);//可不写
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
// int i = statement.executeUpdate(sql);//dml
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String sex = resultSet.getString(3);
Date date = resultSet.getDate(4);
String phone = resultSet.getString(5);
System.out.printf(id+"\t"+name+"\t"+sex+"\t"+date+"\t"+phone);
}
resultSet.close();
statement.close();
connection.close();
resultSet
sql注入
SQL注入：利用系统没有对用户输入数据进行检查，而在用户输入数据中注入非法SQL语句或命令，恶意攻击数据库。
select * from admin where name ='admin' and pwd ='123';
admin--1'or
password--or '1'='1
select * from admin where name ='1'or' and pwd ='or '1'='1';
Statement【存在sql注入问题】
PreparedStatement【预处理】
CallableStatement【存储过程】
PreparedStatement select
String sql ="select * from actor where id =? and name =?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,1);
preparedStatement.setString(2,"孙悟空");
ResultSet resultSet = preparedStatement.executeQuery();//不要再填sql,sql语句不含？的可以填
while(resultSet.next()){
int id = resultSet.getInt(1);//getInt("id");
String name = resultSet.getString(2);
String sex = resultSet.getString(3);
Date date = resultSet.getDate(4);
String phone = resultSet.getString(5);
System.out.println(id+"\t"+name+"\t"+sex+"\t"+date+"\t"+phone);
}
resultSet.close();
preparedStatement.close();
connection.close();
PreparedStatement dml
String sql ="insert into actor values (?,?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//可以setObject
preparedStatement.setInt(1,4);
preparedStatement.setString(2,"沙僧");
preparedStatement.setString(3,"男");
preparedStatement.setDate(4,new java.sql.Date(0,8,8));
preparedStatement.setString(5,"45678");
int i = preparedStatement.executeUpdate();//execute 返回Boolean
System.out.printf(i>0?"success":"default");
preparedStatement.close();
connection.close();
JDBC事务
connection.setAutoCommit(false); //开启事务 ，默认自动提交
connection.rollback();//回滚
connection.commit();//提交
批处理
批处理搭配preparedStatement搭配使用，减少编译次数，减少运行次数，效率大大提高
使用时url 加上
?rewriteBatchedStatements=true
preparedStatement.addBatch();
preparedStatement.executeBatch();
preparedStatement.clearBatch();
//传统
for (int i = 0; i < 5000; i++) {
preparedStatement.setObject(1,"孙悟空"+i);
preparedStatement.executeUpdate();
}
//批处理
for (int i = 0; i < 5000; i++) {
preparedStatement.setObject(1,"孙悟空"+i);
preparedStatement.addBatch();
if((i+1)%1000==0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
数据库连接池
传统的连接次数太多 too many connections
传统获取connection问题
连接池
连接池种类
C3P0
public class C3P0 {
@Test
public void test01() throws Exception {
//1.创建一个数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//2.获取配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
//3.给数据源设置相关的参数
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setPassword(password);
comboPooledDataSource.setUser(user);
//4.初始化连接数
comboPooledDataSource.setInitialPoolSize(10);
comboPooledDataSource.setMaxPoolSize(50);//最大连接数
long start = System.currentTimeMillis();
//批处理
for (int i = 0; i < 500000; i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("用时:"+ (end-start));
}
@Test
//c3p0-config.xml 拷贝到src下
public void test02() throws Exception {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("test");
long start = System.currentTimeMillis();
for (int i = 0; i < 500000; i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("用时:"+ (end-start));
}
}
Druid
public class Druid {
@Test
public void test01() throws Exception{
//1.引入jar包
//2.配置文集druid.properties
//3.读取配合文件
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
//druid连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
for (int i = 0; i < 500000; i++) {
Connection connection = dataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("用时:"+ (end-start));
}
@Test
public void test02() throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
connection = JDBCUtilsByDruid.getConnection();
String sql = "select * from abc where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,1);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.print("id: "+id + " name:"+name);
}
JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
}
}
DruidUtils
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtilsByDruid {
private static DataSource dataSource;
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//不是关闭mysql连接 是放回连接池
public static void close(ResultSet resultSet, Statement statement,Connection connection){
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}catch (Exception e){
throw new RuntimeException(e);
}
}
}
Apache-DBUtils
connection关闭后 ResultSet无法使用 等
DBUtils_query
//返回多个对象
List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class),4);
//返回 单个对象
Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 4);
// 返回单行单列
Object o = queryRunner.query(connection, sql, new ScalarHandler(), 4);
@Test
public void dbutils_query() throws Exception {
//1.得到druid
Connection connection = JDBCUtilsByDruid.getConnection();
//创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
//执行方法 Arraylist
String sql = " select * from actor ";
//new BeanListHandler<>(Actor.class) 反射机制获取Actor类属性 进行封装
//id=4.可变参数 可以多个
//底层 resultset 会在query 关闭 preparedStatement
List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class),4);
//返回 单个对象
// Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 4);
// 返回单行单列
// Object o = queryRunner.query(connection, sql, new ScalarHandler(), 4);
for (Actor actor : list) {
System.out.println(actor);
}
JDBCUtilsByDruid.close(null,null,connection);
}
/**
* 分析 queryRunner.query方法:
* public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
* PreparedStatement stmt = null;//定义PreparedStatement
* ResultSet rs = null;//接收返回的 ResultSet
* Object result = null;//返回ArrayList
*
* try {
* stmt = this.prepareStatement(conn, sql);//创建PreparedStatement
* this.fillStatement(stmt, params);//对sql 进行 ? 赋值
* rs = this.wrap(stmt.executeQuery());//执行sql,返回resultset
* result = rsh.handle(rs);//返回的resultset --> arrayList[result] [使用到反射，对传入class对象处理]
* } catch (SQLException var33) {
* this.rethrow(var33, sql, params);
* } finally {
* try {
* this.close(rs);//关闭resultset
* } finally {
* this.close((Statement)stmt);//关闭preparedstatement对象
* }
* }
*
* return result;
* }
*/
BasicDao
对表的增删改查
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class BasicDao<T> {
private QueryRunner queryRunner = new QueryRunner();
//dml
public int update(String sql,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.update(connection,sql,parameters);
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
//返回多个对象
public List<T> queryMulti(String sql,Class<T> tClass,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection,sql,new BeanListHandler<T>(tClass),parameters);
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
//返回单行
public T querySingle(String sql,Class<T> tClass,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection,sql,new BeanHandler<T>(tClass),parameters);
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
//返回单值
public Object queryScalar(String sql,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection,sql,new ScalarHandler(),parameters);
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
}
test
@Test
public void test(){
update("update actor set sex ='男' where id =?",3);
update("insert into actor values(?,?,?,?,?)",5,"唐僧","男",new Date(100,10,10),"129456");//1900+100,10+1
update("delete from actor where id = ?",4);
List<Actor> actors = queryMulti("select * from actor", Actor.class);
for (Actor actor : actors) {
System.out.println(actor);
}
Actor actor = querySingle("select * from actor where id =?", Actor.class, 2);
System.out.println(actor);
Object o = queryScalar("select name from actor where id =?", 5);
System.out.println(o);
}
2070
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
