JDBC
基本介绍
JDBC原理
模拟JDBC
public class MysqlJdbcImp implements JdbcInterface{
@Override
public Object getConnection() {
System.out.println("得到mysql的连接" );
return null;
}
@Override
public void crud() {
System.out.println("得到mysql的增删改查" );
}
@Override
public void close() {
System.out.println("关闭连接" );
}
}
public interface JdbcInterface {
//连接
public Object getConnection();
//crud操作
public void crud();
//关闭连接
public void close();
}
public class Test {
public static void main(String[] args) {
JdbcInterface jdbcInterface = new MysqlJdbcImp();
jdbcInterface.getConnection();//通过接口来调用实现类
jdbcInterface.crud();
jdbcInterface.close();
}
}
JDBC的API
JDBC程序编写步骤
package com.wcz_.jdbc;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author 王成志
* @version 1.0
*/
public class Jdbc01 {
public static void main(String[] args) throws SQLException {
//1.注册驱动
Driver driver = new Driver();
//2.得到连接
//jdbc:mysql://规定好表示协议, 不能改变
//localhost 表示主机,可以是ip地址
//3306 表示mysql接听端口
String url = "jdbc:mysql://localhost:3306/wcz_db02";
//将用户名和密码封装到properties对象中
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","wcz");
Connection connect = driver.connect(url, properties);//连接
//3.执行sql
String sql = "insert into actor values(null,'刘德华','男','1970-11-11','110' )";
//用于执行静态sql语句并返回其生成的对象和结果
Statement statement = connect.createStatement();
int rows = statement.executeUpdate(sql);//如果是dml语句返回的就是影响行数
System.out.println(rows > 0 ? "成功":"失败");
//4.关闭连接
statement.close();
connect.close();
}
}
JDBC连接数据库的方式
public void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/wcz_db02";
//将用户名和密码封装到properties对象中
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","wcz");
Connection connect = driver.connect(url, properties);//连接
}
//使用DriverManager替代Driver统一管理
public void connect03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
//创建url
String url = "jdbc:mysql://localhost:3306/wcz_db02";
String user = "root";
String password = "wcz";
DriverManager.registerDriver(driver);//注册driver驱动
DriverManager.getConnection(url,user,password);
}
//方式4
public void connect04() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
//创建url
String url = "jdbc:mysql://localhost:3306/wcz_db02";
String user = "root";
String password = "wcz";
Connection connection = DriverManager.getConnection(url, user, password);
}
public void connect05() throws IOException, ClassNotFoundException, SQLException {
//通过properties对象获取信息
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);
}
ResultSet
statement
preparedstatement
JDBCUtils
public class JDBCUtils {
//定义相关属性,只需要一份
private static String user;//用户名
private static String password;//密码
private static String url;//url
private static String driver;//驱动
//在static代码块去初始化
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\mysql.properties"));
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
driver = properties.getProperty("driver");
} catch (IOException e) {
//实际开发中可以这样处理
throw new RuntimeException(e);
}
}
//连接数据库
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException throwables) {
throw new RuntimeException(throwables);
}
}
//关闭相关资源
/*
1.ResultSet结果集
2.Statement 或者 PreparedStatement
3.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 throwables) {
throw new RuntimeException(throwables;
}
}
}
事务
public class Transaction {
@Test
public void noTransaction(){
//不用事务进行操作
PreparedStatement preparedStatement =null;
Connection connection = null;
String sql = "update account1 set balance = balance - 100 where id = 1";
String sql2 = "update account1 set balance = balance + 100 where id = 2";
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
@Test
public void useTransaction(){
//使用事务进行
PreparedStatement preparedStatement =null;
Connection connection = null;
String sql = "update account1 set balance = balance - 100 where id = 1";
String sql2 = "update account1 set balance = balance + 100 where id = 2";
try {
connection = JDBCUtils.getConnection();
//将connection设置为不自动提交
connection.setAutoCommit(false);//相当于开启了事务
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
int i = 1 / 0;
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
} catch (Exception e) {
//在这边进行一个回滚
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
} finally {
}
}
}
批处理
public class Batch {
//演示批处理
public void noBatch() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into admin2 values(null,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, "jack" + i);
preparedStatement.setString(2, "666");
preparedStatement.executeUpdate();
}
JDBCUtils.close(null, preparedStatement, connection);
}
public void useBatch() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into admin2 values(null,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, "jack" + i);
preparedStatement.setString(2, "666");
//将sql语句加入批处理包
preparedStatement.addBatch();
//当有1000条时再执行
if ((i + 1) % 1000 == 0){
preparedStatement.executeBatch();
// 清空
preparedStatement.clearBatch();
}
}
JDBCUtils.close(null, preparedStatement, connection);
}
}
数据库连接池
基本介绍
连接种类
C3P0
//方式1相关参数在程序中指定
@Test
public void testC3P0_01() throws Exception{
//创建一个数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//通过配置文件获取相关信息
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");
//给数据源comboPooledDataSource配置相关参数
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
//设置初始化连接数
comboPooledDataSource.setInitialPoolSize(10);
//最大连接数
comboPooledDataSource.setMaxPoolSize(50);
Connection connection = comboPooledDataSource.getConnection();//核心方法
System.out.println("连接成功");
connection.close();
}
//第二种方法
public void testC3P0_02() throws Exception{
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("wcz_db02");
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
druid
public class Druid_ {
public void testDruid() throws Exception{
//加入配置文件拷贝到src目录
//读取配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
//创建一个数据源
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
connection.close();
}
}
德鲁伊工具类
public class JDBCUtilsByDruid {
private static DataSource ds;
//在静态代码块完成初始化
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//编写getConnection方法
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
//关闭连接,只是把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 throwables) {
throw new RuntimeException(throwables);
}
}
}
Apache——DBUtils
查询
public class DBUtils_Use {
//使用apache的dbutils工具类完成crud
@Test
public void testQueryMany() throws SQLException {
//得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
//使用dbutils的类和接口,先引入jar文件
//创建一个QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 就可以执行相关方法
String sql = "select * from actor where id >= ?";
//query方法就是执行一个sql语句得到一个arraylist
//beanlisthandler用来在将resultset取出来封装到arraylist
//1是用来传给sql语句中的问号
//底层会关闭resultset和statement
List<Actor> list =
queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
for (Actor actor : list) {
System.out.println(actor);//输出集合信息
}
JDBCUtils.close(null,null,connection);
}
}
@Test
//演示返回结果是单行
public void testQuerySimple() throws SQLException {
//得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
//使用dbutils的类和接口,先引入jar文件
//创建一个QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 就可以执行相关方法返回单个对象
String sql = "select * from actor where id = ?";
Actor actor =
queryRunner.query(connection, sql, new BeanHandler<>(Actor.class),4);
System.out.println(actor);
JDBCUtils.close(null,null,connection);
}
DML
@Test
//演示完成dml操作
public void testDML() throws SQLException {
//得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
//使用dbutils的类和接口,先引入jar文件
//创建一个QueryRunner
QueryRunner queryRunner = new QueryRunner();
//组织sql语句完成dml
String sql = "update actor set name = ? where id = ?";
//返回的是生效行数
int affectedRow = queryRunner.update(connection, sql, "rose", 4);
System.out.println(affectedRow > 0?"执行成功":"无影响");
JDBCUtils.close(null,null,connection);
}
BasicDao
public class BasicDAO<T> {//泛型指定具体类型
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 throwables) {
throw new RuntimeException(throwables);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
//返回多行查询
public List<T> queryMulti(String sql,Class<T> clazz,Object...parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection,sql,new BeanListHandler<T>(clazz),parameters);
} catch (SQLException throwables) {
throw new RuntimeException(throwables);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
//查询单行
public T querySimple(String sql, Class<T> clazz , Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection,sql,new BeanHandler<T>(clazz),parameters);
} catch (SQLException throwables) {
throw new RuntimeException(throwables);
}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 throwables) {
throw new RuntimeException(throwables);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
}
public class TestDAO {
//测试
@Test
public void testActorDAO(){
ActorDAO actorDAO = new ActorDAO();
//查询语句
List<Actor> actors = actorDAO.queryMulti("select * from actor where id >= ?", Actor.class, 1);
for (Actor actor : actors) {
System.out.println(actor);
}
}
}