JDBC工具类
DBUtils使用QueryRunner类update方法实现对数据表的增删改
package JDBCUtilsDemo;
//:创建一个自动生成数据库连接的类
import java.sql.Connection;
import java.sql.DriverManager;
public class JDBCUtilsDemo {
private static Connection con;
public JDBCUtilsDemo() {
}
public static Connection getCon() {
return con;
}
static {
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:33060/mybase";
String name = "root";
String password = "root";
con = DriverManager.getConnection(url, name, password);
}catch (Exception e){
e.printStackTrace();
System.out.println("数据库连接失败!");
}
}
}
package cn.itcast.demo;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import jdbcUtilsDemo.JDBCUtilsDemo;
/**
* 使用QueryRunner类实现对数据表的
* insert delete update
* 调用QueryRunner类的方法 UpDate(Connection con,String sql,Object...param)
* Object...param 可变参数,Object类型,SQL语句会出现?占位符
* 数据库连接对象,自定义的工具类传递
*/
public class QueryRunnerDemo {
private static Connection con = JDBCUtilsDemo.getCon();
public static void main(String[] args) throws SQLException {
//insert();
// update();
delete();
}
/*
* 定义方法,使用QueryRunner类中的 方法update将数据表中的数据修改
*/
public static void delete() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "DELETE FROM mybase where midnum=?";
Object[] params = {5};
qr.update(con, sql, params);
DbUtils.closeQuietly(con);
}
/*
* 定义方法,使用QueryRunner类中的方法update将数据表中的数据修改
*/
public static void update() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "UPDATE mybase SET midnum=?,mname=?,sex=? WHERE midnum=?";
Object[] params = {5,"你好a","女",6};
int row = qr.update(con, sql, params);
System.out.println(row);
DbUtils.closeQuietly(con);
}
/*
* 定义方法,使用QueryRunner类的方法update向数据表中添加数据
*/
public static void insert() throws SQLException {
//创建QueryRunner类对象
QueryRunner qr = new QueryRunner();
String sql = "INSERT INTO mybase (midnum,mname,sex) VALUES(?,?,?)";
Object[] param = {5,"王瑞","女"};
//调用QueryRunner类的方法update,执行SQL语句
int row = qr.update(con, sql, param);
System.out.println(row);
DbUtils.closeQuietly(con);
}
}
DBUtils使用QueryRunner类的query方法实现对数据表的查询
package cn.itcast.demo;
/**
* QueryRunner数据查询操作:
* 调用QueryRunner累的方法query(Connection con,String sql,ResultsetHandler r,Object...parms)
* Object...parms sql语句中的?占位符
*
* 注意:query方法的返回值,返回的是T泛型.具体返回值类型,跟随结果处理方式变化
*/
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.sun.org.apache.bcel.internal.generic.NEW;
import jdbcUtilsDemo.JDBCUtilsConfig;
public class QueryRunnerDemo1 {
private static Connection con = JDBCUtilsConfig.getConnection();
public static void main(String[] args) throws SQLException {
// arrayHandler();
// arrayListHandler();
//beanHandler();
beanListHandler();
}
/*
* 结果集的第四种方式,BeanListHandler
*/
public static void beanListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM mybase";
List<Mybase> mybase = qr.query(con, sql, new BeanListHandler<Mybase>(Mybase.class));
for(Mybase m:mybase) {
System.out.println(m);
}
}
/*
* 结果集的第三种方式,BeanHandler 将结果集的第一行数据封装成JavaBean对象
*/
public static void beanHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM mybase";
Mybase bean = qr.query(con, sql, new BeanHandler<Mybase>(Mybase.class));
System.out.println(bean);
}
/*
* 结果集的第二种方式,ArrayListHandler 将结果集的每一行封装到对象数组中 将对象数组存储到list集合
*/
public static void arrayListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM mybase";
java.util.List<Object[]> list = qr.query(con, sql, new ArrayListHandler());
for (Object[] obj : list) {
for (Object o : obj) {
System.out.println(o);
}
}
}
/*
* 结果集第一种处理方式,ArrayHandler 将结果集的第一行存储到数组中 Object[]
*/
public static void arrayHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM mybase";
Object[] resultSet = qr.query(con, sql, new ArrayHandler());
for (Object o : resultSet) {
System.out.println(o);
}
}
}
javax.sql.DataSource接口
package dbcp;
import java.sql.Connection;
import org.apache.commons.dbcp.BasicDataSource;
/**
* 连接池jar包中,定义好一个类BasicDateSource
* 该类实现类数据源的规范接口,javax.sql.DataSource
*/
public class DataSourceDemo {
public static void main(String[] args) {
//创建DataSource实现类对象
BasicDataSource dateSource = new BasicDataSource();
dateSource.setDriverClassName("com.mysql.jdbc.Driver");
dateSource.setUrl("jdbc:mysql://localhost:33060/mybase");
dateSource.setUsername("root");
dateSource.setPassword("root");
try {
Connection con = dateSource.getConnection();
System.out.println(con);
}catch (Exception e) {
e.printStackTrace();
System.out.println("数据库连接失败");
}
}
}
数据库连接池
DBCP连接池
package dbcp;
/**
* 使用DBCP实现数据库连接池
* 连接池配置,自定义类
* 最基本四项完整
* 对于数据库连接池其他配置,自定义
*/
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
public class DBCPUtils {
private static BasicDataSource datasource = new BasicDataSource();
static {
datasource.setDriverClassName("com.mysql.jdbc.Driver");
datasource.setUrl("jdbc:mysql://localhost:33060/mybase");
datasource.setUsername("root");
datasource.setPassword("root");
//对象连接池中的连接数量配置
datasource.setInitialSize(10); //初始化连接数
datasource.setMaxActive(8); //最大连接数量
datasource.setMaxIdle(5); //最大空闲数
datasource.setMinIdle(1); //最小空闲数
}
public static DataSource getDatasource() {
return datasource;
}
}
***************************************************************
package dbcp;
/**
* QueryRunner类的构造方法,接收DataSource接口的实现类
* 后面调用方法update,query无需传递他们的connection连接对象
*/
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
public class QueryRunnerDemo {
private static QueryRunner qr = new QueryRunner(DBCPUtils.getDatasource());
public static void main(String[] args) {
select();
}
public static void select() {
String sql = "SELECT * FROM mybase";
try {
List<Object[]> list = qr.query(sql, new ArrayListHandler() );
for(Object object[] : list) {
for(Object o : object) {
System.out.print(o+" ");
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}