广东省建设工程网站,专业的网站开发服务,成都景观设计公司排名,网站排名要怎么做文章目录 1.批处理应用1.基本介绍2.批处理演示1.创建测试表2.修改url3.编写java代码 3.批处理源码分析 2.数据库连接池1.传统连接弊端分析2.数据库连接池基本介绍1.概念介绍2.数据库连接池示意图3.数据库连接池种类 3.C3P0连接池1.环境配置1.导入jar包2.将整个lib添加到项目中3… 文章目录 1.批处理应用1.基本介绍2.批处理演示1.创建测试表2.修改url3.编写java代码 3.批处理源码分析 2.数据库连接池1.传统连接弊端分析2.数据库连接池基本介绍1.概念介绍2.数据库连接池示意图3.数据库连接池种类 3.C3P0连接池1.环境配置1.导入jar包2.将整个lib添加到项目中3.配置代码提示 2.C3P0方式一java程序3.C3P0方式二配置文件1.环境配置1.将c3p0-config.xml配置文件复制到src目录下2.修改配置文件的参数 2.编写java代码 4.德鲁伊连接池1.环境配置1.导入jar包2.将配置文件复制到src目录下名字任意3.修改配置文件的参数 2.编写java代码 5.德鲁伊工具类1.编写代码2.测试使用 6.Apache——DBUtils1.引出2.基本介绍3.Apache——DBUtils查询1.添加依赖2.编写java代码1.Actor.java(封装每一行的bean)2.查询多条记录 3.查询单条记录4.查询单行单列记录 4.DML操作 7.BasicDao1.引出2.BasicDao分析3.代码实现1.文件目录2.BasicDao3.ActorDao4.Actor5.TestDao6.JDBCUtilsByDruid7.druid.properties 1.批处理应用
1.基本介绍 2.批处理演示 1.创建测试表
-- 创建的测试表
CREATE TABLE admin2(id INT PRIMARY key auto_increment,username VARCHAR(32) NOT NULL,PASSWORD VARCHAR(32) NOT NULL
)
-- 查看表数据
SELECT * FROM admin2
-- 查看行数
SELECT count(*) FROM admin22.修改url ?rewriteBatchedStatementstrue //添加这行代码3.编写java代码
package jdbc_;import org.junit.jupiter.api.Test;
import utils.JDBCUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;/*** author 孙显圣* version 1.0*/
public class Batch_ {Testpublic void testDML() {//建立连接Connection connection JDBCUtils.getConnection();//编写sql语句进行插入String sql insert into admin2 values (null, ?, ?);PreparedStatement preparedStatement null;try {preparedStatement connection.prepareStatement(sql);//循环进行预处理并添加到处理包中for (int i 0; i 5000; i) {preparedStatement.setString(1, Tom);preparedStatement.setString(2, 666666);//1.添加到处理包中先不执行preparedStatement.addBatch();if ((i 1) % 1000 0) {//2.每执行1000次则执行一次preparedStatement.executeBatch();//3.执行之后清空处理包中的sql语句preparedStatement.clearBatch();}}} catch (SQLException e) {throw new RuntimeException(e);} finally {//关闭资源JDBCUtils.close(null, preparedStatement, connection);}}
}
3.批处理源码分析 2.数据库连接池
1.传统连接弊端分析 2.数据库连接池基本介绍
1.概念介绍 2.数据库连接池示意图 3.数据库连接池种类 3.C3P0连接池
1.环境配置
1.导入jar包 2.将整个lib添加到项目中 3.配置代码提示 2.C3P0方式一java程序 Testpublic void testC3P01() throws Exception {//1.创建一个数据源对象可以理解为这个数据源对象就是那个连接池ComboPooledDataSource comboPooledDataSource new ComboPooledDataSource();//2.读取配置文件获取urluserpassworddriverProperties properties new Properties();properties.load(new FileInputStream(src\\mysql.properties));String url properties.getProperty(url);String user properties.getProperty(user);String password properties.getProperty(password);String driver properties.getProperty(driver);//3.给数据源设置相关参数comboPooledDataSource.setJdbcUrl(url);comboPooledDataSource.setUser(user);comboPooledDataSource.setPassword(password);comboPooledDataSource.setDriverClass(driver);//4.设置初始化参数comboPooledDataSource.setInitialPoolSize(10); //初始化连接数comboPooledDataSource.setMaxPoolSize(50); //最大连接数//5.获取连接Connection connection comboPooledDataSource.getConnection();System.out.println(连接OK);//6.关闭连接connection.close();}3.C3P0方式二配置文件
1.环境配置
1.将c3p0-config.xml配置文件复制到src目录下 c3p0-config!-- 数据源名称可以随意--named-config namehello!-- 驱动类 --property namedriverClasscom.mysql.cj.jdbc.Driver/property!-- url--property namejdbcUrljdbc:mysql://localhost:3306/hsp_db02/property!-- 用户名 --property nameuserroot/property!-- 密码 --property namepasswordroot/property!-- 每次增长的连接数--property nameacquireIncrement5/property!-- 初始的连接数 --property nameinitialPoolSize10/property!-- 最小连接数 --property nameminPoolSize5/property!-- 最大连接数 --property namemaxPoolSize50/property!-- 可连接的最多的命令对象数 --property namemaxStatements5/property!-- 每个连接对象可连接的最多的命令对象数 --property namemaxStatementsPerConnection2/property/named-config
/c3p0-config2.修改配置文件的参数 2.编写java代码 Testpublic void testC3P02() throws Exception {//1.创建与配置文件名称相同的数据源ComboPooledDataSource comboPooledDataSource new ComboPooledDataSource(hello); //注意这里的hello是配置文件中的名字//2.获取连接Connection connection comboPooledDataSource.getConnection();System.out.println(连接OK);//3.关闭连接connection.close();}4.德鲁伊连接池
1.环境配置
1.导入jar包 2.将配置文件复制到src目录下名字任意 #keyvalue
driverClassNamecom.mysql.jdbc.Driver
urljdbc:mysql://localhost:3306/girls?rewriteBatchedStatementstrue
#urljdbc:mysql://localhost:3306/girls
usernameroot
passwordroot
#initial connection Size
initialSize10
#min idle connecton size
minIdle5
#max active connection size
maxActive50
#max wait time (5000 mil seconds)
maxWait50003.修改配置文件的参数 2.编写java代码
package datasource;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;/*** author 孙显圣* version 1.0*/
public class Druid_ {public static void main(String[] args) throws Exception {//1.读取配置文件Properties properties new Properties();properties.load(new FileInputStream(src\\druid.properties));//2.创建数据源对象就是连接池将配置文件传进去DataSource dataSource DruidDataSourceFactory.createDataSource(properties);//3.获取连接Connection connection dataSource.getConnection();System.out.println(连接OK);//4.关闭连接connection.close();}
}
5.德鲁伊工具类
1.编写代码
package utils;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;/*** author 孙显圣* version 1.0*/
public class JDBCUtilsByDruid {//静态数据源引用jdbc的接口private static DataSource dataSource;//静态代码块在类加载时为数据源引用赋值static {//1.读取配置文件Properties properties new Properties();try {properties.load(new FileInputStream(src\\druid.properties));} catch (IOException e) {throw new RuntimeException(e);}//2.使用配置文件创建德鲁伊数据源对象try {dataSource DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {throw new RuntimeException(e);}}//编写getConnection方法public static Connection getConnection() {try {return dataSource.getConnection();} catch (SQLException e) {throw new RuntimeException(e);}}//把Connection对象放回连接池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 (SQLException e) {throw new RuntimeException(e);}}}
2.测试使用
package datasource;import org.junit.jupiter.api.Test;
import utils.JDBCUtilsByDruid;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;/*** author 孙显圣* version 1.0*/
public class DruidUtils_Use {Testpublic void testSelect() {//建立连接Connection connection JDBCUtilsByDruid.getConnection();//编写sql语句进行查询String sql select name, phone from actor where id ?;PreparedStatement preparedStatement null;ResultSet resultSet null;try {//进行预处理preparedStatement connection.prepareStatement(sql);preparedStatement.setInt(1, 8);//执行查询resultSet preparedStatement.executeQuery();while (resultSet.next()) {String string resultSet.getString(name);String string1 resultSet.getString(phone);System.out.println(string string1);}} catch (SQLException e) {throw new RuntimeException(e);} finally {//关闭资源JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);}}}
6.Apache——DBUtils
1.引出 2.基本介绍 3.Apache——DBUtils查询 1.添加依赖 2.编写java代码
1.Actor.java(封装每一行的bean)
package datasource;import java.sql.Timestamp;/*** author 孙显圣* version 1.0* 这是一个bean用来封装actor表的每一行数据*/
public class Actor {private Integer id; //注意要使用包装类private String name;private String sex;private Timestamp borndate; //mysql8只能用这个类型来接受datetimeprivate String phone;public Actor() { //一定要给一个无参构造器[反射需要]}public Actor(Integer id, String name, String sex, Timestamp borndate, String phone) {this.id id;this.name name;this.sex sex;this.borndate borndate;this.phone phone;}public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public String getName() {return name;}public void setName(String name) {this.name name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex sex;}public Timestamp getBorndate() {return borndate;}public void setBorndate(Timestamp borndate) {this.borndate borndate;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone phone;}Overridepublic String toString() {return \nActor{ id id , name name \ , sex sex \ , borndate borndate , phone phone \ };}}
2.查询多条记录
new BeanListHandler(Actor.class)
package datasource;import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.jupiter.api.Test;
import utils.JDBCUtilsByDruid;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;/*** author 孙显圣* version 1.0*/
public class DBUtils_USE {Testpublic void testQueryMan() throws SQLException {//1.得到连接Connection connection JDBCUtilsByDruid.getConnection();//2.创建QueryRunnerQueryRunner queryRunner new QueryRunner();//3.编写sqlString sql select borndate from actor where id ?;//4.调用方法返回ArrayList结果集其中每一个元素都是表的一行封装到了bean中ListActor query queryRunner.query(connection, sql, new BeanListHandler(Actor.class), 1); //1就是给里面的问号赋值//5.获取每一行的beanfor (Actor actor : query) {System.out.println(actor);}//6.关闭连接他会自动关闭resultset和preparedStatementJDBCUtilsByDruid.close(null, null, connection);}}
3.查询单条记录
new BeanHandler(Actor.class) Testpublic void testQuerySingle() {//1.获取连接Connection connection JDBCUtilsByDruid.getConnection();//2.创建queryRunnerQueryRunner queryRunner new QueryRunner();//3.编写sqlString sql select * from actor where id ?;//4.调用查询方法Actor query null;try {query queryRunner.query(connection, sql, new BeanHandler(Actor.class), 8);} catch (SQLException e) {throw new RuntimeException(e);}System.out.println(query);//5.关闭资源try {connection.close();} catch (SQLException e) {throw new RuntimeException(e);}}4.查询单行单列记录
new ScalarHandler() Testpublic void testScalar() {//1.获取连接Connection connection JDBCUtilsByDruid.getConnection();//2.创建queryRunnerQueryRunner queryRunner new QueryRunner();//3.编写sqlString sql select name from actor where id ?; //单行单列//4.查询try {Object query queryRunner.query(connection, sql, new ScalarHandler(), 8);System.out.println(query);} catch (SQLException e) {throw new RuntimeException(e);}//5.关闭资源try {connection.close();} catch (SQLException e) {throw new RuntimeException(e);}}
4.DML操作 Testpublic void testDML() throws SQLException {//1.获取连接Connection connection JDBCUtilsByDruid.getConnection();//2.创建queryRunnerQueryRunner queryRunner new QueryRunner();//3.编写sql//增加String sql1 insert into actor values(?, ?, ?, ?, ?);//删除String sql2 delete from actor where id ?;//修改String sql3 update actor set phone ?;//4.执行sqlint update queryRunner.update(connection, sql1, null, 张三丰, 男, 2005-11-02, 51552);int update1 queryRunner.update(connection, sql2, 11);int update2 queryRunner.update(connection, sql3, 123456);//5.关闭资源try {connection.close();} catch (SQLException e) {throw new RuntimeException(e);}}7.BasicDao
1.引出 2.BasicDao分析 3.代码实现
1.文件目录 2.BasicDao
package 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 utils.JDBCUtilsByDruid;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;/*** author 孙显圣* version 1.0* 开发BasicDAO , 是其他DAO的父类*/
public class BasicDaoT { //泛型指定具体类型private QueryRunner qr new QueryRunner();//开发通用的dml方法, 针对任意的表public int update(String sql, Object... parameters) {Connection connection null;try {connection JDBCUtilsByDruid.getConnection();int update qr.update(connection, sql, parameters);return update;} catch (SQLException e) {throw new RuntimeException(e); //将编译异常-运行异常 ,抛出} finally {JDBCUtilsByDruid.close(null, null, connection);}}//返回多个对象(即查询的结果是多行), 针对任意表/**** param sql sql 语句可以有 ?* param clazz 传入一个类的Class对象 比如 Actor.class* param parameters 传入 ? 的具体的值可以是多个* return 根据Actor.class 返回对应的 ArrayList 集合*/public ListT queryMulti(String sql, ClassT clazz, Object... parameters) {Connection connection null;try {connection JDBCUtilsByDruid.getConnection();return qr.query(connection, sql, new BeanListHandlerT(clazz), parameters);} catch (SQLException e) {throw new RuntimeException(e); //将编译异常-运行异常 ,抛出} finally {JDBCUtilsByDruid.close(null, null, connection);}}//查询单行结果 的通用方法public T querySingle(String sql, ClassT clazz, Object... parameters) {Connection connection null;try {connection JDBCUtilsByDruid.getConnection();return qr.query(connection, sql, new BeanHandlerT(clazz), parameters);} catch (SQLException 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 qr.query(connection, sql, new ScalarHandler(), parameters);} catch (SQLException e) {throw new RuntimeException(e); //将编译异常-运行异常 ,抛出} finally {JDBCUtilsByDruid.close(null, null, connection);}}}
3.ActorDao
package BasicDao_.dao;import BasicDao_.domain.Actor;/*** author 孙显圣* version 1.0*/
public class ActorDao extends BasicDaoActor{//拥有BasicDao所有的方法//根据业务需求可以写上特有的方法
}
4.Actor
package BasicDao_.domain;import java.sql.Timestamp;/*** author 孙显圣* version 1.0* 这是一个bean用来封装actor表的每一行数据*/
public class Actor {private Integer id;private String name;private String sex;private Timestamp borndate; //mysql8只能用这个类型来接受datetimeprivate String phone;public Actor() { //一定要给一个无参构造器[反射需要]}public Actor(Integer id, String name, String sex, Timestamp borndate, String phone) {this.id id;this.name name;this.sex sex;this.borndate borndate;this.phone phone;}public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public String getName() {return name;}public void setName(String name) {this.name name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex sex;}public Timestamp getBorndate() {return borndate;}public void setBorndate(Timestamp borndate) {this.borndate borndate;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone phone;}Overridepublic String toString() {return \nActor{ id id , name name \ , sex sex \ , borndate borndate , phone phone \ };}}
5.TestDao
package BasicDao_.test;import BasicDao_.dao.ActorDao;
import BasicDao_.domain.Actor;import java.util.List;/*** author 孙显圣* version 1.0*/
public class TestDao {public static void main(String[] args) {//测试一下ActorDao对actor表的操作ActorDao actorDao new ActorDao();//1.查询ListActor actors actorDao.queryMulti(select * from actor where id ?, Actor.class, 2);for (Actor actor : actors) {System.out.println(actor);}//2.查询单行记录Actor actor actorDao.querySingle(select * from actor where id ?, Actor.class, 4);System.out.println(actor);//3.查询单行单列记录Object o actorDao.queryScalar(select name from actor where id ?, 9);System.out.println(o);//4.增加一条记录int update actorDao.update(insert into actor values(?,?,?,?,?), null, 王五, 女, 2002-1-9, 5455555);System.out.println(update 0 ? 成功 : 失败);//5.删除一条记录int update1 actorDao.update(delete from actor where id ?, 10);System.out.println(update1 0 ? 成功 : 失败);//6.修改一条记录int update2 actorDao.update(update actor set name ?, 女);System.out.println(update2 0 ? 成功 : 失败);}
}
6.JDBCUtilsByDruid
package BasicDao_.utils;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;/*** author 孙显圣* version 1.0*/
public class JDBCUtilsByDruid {//静态数据源引用jdbc的接口private static DataSource dataSource;//静态代码块在类加载时为数据源引用赋值static {//1.读取配置文件Properties properties new Properties();try {properties.load(new FileInputStream(src\\druid.properties));} catch (IOException e) {throw new RuntimeException(e);}//2.使用配置文件创建德鲁伊数据源对象try {dataSource DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {throw new RuntimeException(e);}}//编写getConnection方法public static Connection getConnection() {try {return dataSource.getConnection();} catch (SQLException e) {throw new RuntimeException(e);}}//把Connection对象放回连接池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 (SQLException e) {throw new RuntimeException(e);}}}
7.druid.properties
#keyvalue
driverClassNamecom.mysql.cj.jdbc.Driver
urljdbc:mysql://localhost:3306/hsp_db02?rewriteBatchedStatementstrue
#urljdbc:mysql://localhost:3306/girls
usernameroot
passwordroot
#initial connection Size
initialSize10
#min idle connecton size
minIdle5
#max active connection size
maxActive50
#max wait time (5000 mil seconds)
maxWait5000
