JDBC、DBUtils
JDBC(Java Data Base Connectivity) java数据连接
可以为多种数据库，提供统一访问，它由一组用java语言编写的类和接口组成，也是java访问数据库的规范。
mysql数据库驱动程序jar包
选 connector/j/ 下载
导入mysql数据库驱动程序jar包
file->project structure->modules->添加 jars or directories ->选择目标.jar
增删改 executeUpdate
String driverName = "com.mysql.jdbc.Driver";
//数据库地址 jdbc:mysql://主机IP:端口号/数据库名字
String url = "jdbc:mysql://localhost:3306/david2018_db";
String user = "root";
String password = "1234";
try {
//1.注册驱动
Class.forName(driverName);
//2.连接数据库
Connection conn = DriverManager.getConnection(url, user, password);
//3.获得执行对象
Statement stat = conn.createStatement();
//4.执行insert语句
String sql = "insert into users (uname,uaddress,tel) values ('baidawei','changpingqu','18755488125')";
int result = stat.executeUpdate(sql);
System.out.println("插入"+result+"条数据");
//5.释放资源
stat.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
查询数据 executeQuery
String driverName = "com.mysql.jdbc.Driver";
//数据库地址 jdbc:mysql://主机IP:端口号/数据库名字
String url = "jdbc:mysql://localhost:3306/david2018_db";
String user = "root";
String password = "1234";
try {
//1.注册驱动
Class.forName(driverName);
//2.连接数据库
Connection conn = DriverManager.getConnection(url, user, password);
//3.获得执行对象
Statement stat = conn.createStatement();
//4.执行select语句
String sql = "select * from users";
ResultSet rs = stat.executeQuery(sql);
while(rs.next()){
System.out.print(rs.getInt("uid"));
System.out.print(rs.getString("uname"));
System.out.println(rs.getString("uaddress"));
}
//5.释放资源
rs.close();
stat.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
PrepareStatement预编译 防 or 1=1 sql注入
String driverName = "com.mysql.jdbc.Driver";
//数据库地址 jdbc:mysql://主机IP:端口号/数据库名字
String url = "jdbc:mysql://localhost:3306/david2018_db";
String user = "root";
String password = "1234";
try {
//1.注册驱动
Class.forName(driverName);
//2.连接数据库
Connection conn = DriverManager.getConnection(url, user, password);
//3.获得执行对象
Statement stat = conn.createStatement();
//4.执行select语句
//String id = "5";
//String sql = "select * from users where uid > " + id;
String sql = "select * from users where uid > ?";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setObject(1,5); //从1开始
ResultSet rs = pst.executeQuery(); //这里是pst的executeQuery()无参方法
while(rs.next()){
System.out.print(rs.getInt("uid"));
System.out.print(rs.getString("uname"));
System.out.println(rs.getString("uaddress"));
}
//5.释放资源
rs.close();
stat.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
修改操作
//4.执行update语句
String sql = "update users set uname = ? where uid = ?";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setObject(1,"david");
pst.setObject(2,7);
int result = pst.executeUpdate();
System.out.println(result);
封装JDBCUtils工具类
import java.sql.*;
public class JDBCUtils {
private JDBCUtils(){}
private static Connection con ;
static{
try{
String driverName = "com.mysql.jdbc.Driver";
//数据库地址 jdbc:mysql://主机IP:端口号/数据库名字
String url = "jdbc:mysql://localhost:3306/david2018_db";
String user = "root";
String password = "1234";
//1.注册驱动
Class.forName(driverName);
//2.连接数据库
con = DriverManager.getConnection(url, user, password);
}catch(Exception ex){
throw new RuntimeException(ex+"数据库连接失败");
}
}
public static Connection getConnection(){
return con;
}
public static void close(Connection con,Statement stat){
if(stat!=null){
try{
stat.close();
}catch(SQLException ex){}
}
if(con!=null){
try{
con.close();
}catch(SQLException ex){}
}
}
public static void close(Connection con,Statement stat , ResultSet rs){
if(rs!=null){
try{
rs.close();
}catch(SQLException ex){}
}
if(stat!=null){
try{
stat.close();
}catch(SQLException ex){}
}
if(con!=null){
try{
con.close();
}catch(SQLException ex){}
}
}
}
测试工具类
public static void main(String[] args) {
Connection con = JDBCUtils.getConnection();
PreparedStatement pst = null;
try {
pst = con.prepareStatement("select * from users");
ResultSet rs = pst.executeQuery();
List<users> list = new ArrayList<users>();
while (rs.next()) {
users u = new users();
u.uid = rs.getInt("uid");
u.uname = rs.getString("uname");
u.uaddress = rs.getString("uaddress");
u.utel=rs.getString("tel");
list.add(u);
}
for(users u : list){
System.out.println(u.toString());
}
JDBCUtils.close(con, pst, rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
配置文件
src目录下新建database.properties文件
driverName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/david2018_db
user=root
password=1234
加载配置文件
//类加载器
InputStream in = 当前类名.class.getClassLoader().getResourceAsStream("database.properties");
Properties pro = new Properties();
pro.load(in);
String driverName = pro.getProperty("driverName");
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
连接mysql方法
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class JDBCUtilsConfig {
private static Connection con;
static {
InputStream in = JDBCUtilsConfig.class.getClassLoader().getResourceAsStream("database.properties");
Properties pro = new Properties();
try {
pro.load(in);
String driverName = pro.getProperty("driverName");
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
Class.forName(driverName);
con = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getCon() {
return con;
}
}
DBUtils
简化JDBC开发
下载DBUtils jar包
command+; 导入jar包
QueryRunner.update 增删改
新增：
Connection con = JDBCUtilsConfig.getCon();
QueryRunner qr = new QueryRunner();
String sql = "insert into users (uname,uaddress,tel) values (?,?,?)";
Object[] params = {"davidddd","bj","15156156156156"};
int result = qr.update(con,sql,params);
System.out.println("添加"+result+"条数据");
DbUtils.closeQuietly(con);
修改：
Connection con = JDBCUtilsConfig.getCon();
QueryRunner qr = new QueryRunner();
String sql = "update users set uname = ?,uaddress = ? where uid = ?";
Object[] params = {"david1","sh",8};
int result = qr.update(con,sql,params);
System.out.println("修改"+result+"条数据");
DbUtils.closeQuietly(con);
删除：
Connection con = JDBCUtilsConfig.getCon();
QueryRunner qr = new QueryRunner();
String sql = "delete from users where uid = ?";
int result = qr.update(con,sql,8);
System.out.println("删除"+result+"条数据");
DbUtils.closeQuietly(con);
QueryRunner.query 查询
查询：
ArrayHandler 返回第一行Object[]
ArrayListHandler 返回List<Object[]>
Connection con = JDBCUtilsConfig.getCon();
QueryRunner qr = new QueryRunner();
String sql = "select * from users where uid > ?";
List<Object[]> result = qr.query(con,sql,new ArrayListHandler(),1);
for(Object[] obj : result){
for(Object o : obj){
System.out.println(o);
}
}
DbUtils.closeQuietly(con);
BeanHandler 返回第一行JavaBean对象
BeanListHandler 返回List JavaBean对象
Connection con = JDBCUtilsConfig.getCon();
QueryRunner qr = new QueryRunner();
String sql = "select * from users where uid > ?";
List<users> result = qr.query(con,sql,new BeanListHandler<users>(users.class),1);
for(users u : result){
System.out.println(u);
}
DbUtils.closeQuietly(con);
ColumnListHandler 返回List Object对象 按列查询
ScalarHandler 返回第一条数据 适合count
Connection con = JDBCUtilsConfig.getCon();
QueryRunner qr = new QueryRunner();
String sql = "select count(*) from users where uid > ?";
Long count = qr.query(con,sql,new ScalarHandler<Long>(),1);
System.out.println(count);
DbUtils.closeQuietly(con);
MapHandler 返回第一个Map对象 key value
MapListHandler 返回ListMap对象
Connection con = JDBCUtilsConfig.getCon();
QueryRunner qr = new QueryRunner();
String sql = "select * from users where uid > ?";
List<Map<String, Object>> result = qr.query(con,sql,new MapListHandler(),1);
for(Map<String,Object> map : result){
System.out.println(map.get("uid"));
}
DbUtils.closeQuietly(con);
连接池
共享Connection，避免频繁创建连接、释放连接操作。
用连接池来管理Connection，可以重复使用COnnection，不用我们自己创建Connection，
通过连接池来创建Connection对象，关闭也不会真正的关闭连接，而是吧Connection归还给它，连接池就可以再利用这个对象了。
DBCP
数据库连接池是apache上的一个Java连接池项目，DBCP所依赖的jar包
dbcp.jar poll.jar logging.jar 这三个jar包
导入jar包 command+;
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/david2018_db");
dataSource.setUsername("root");
dataSource.setPassword("1234");
dataSource.setInitialSize(10);//初始化连接数
dataSource.setMaxIdle(5); //最大空闲
dataSource.setMinIdle(1); //最小空闲
Connection con = dataSource.getConnection();
QueryRunner qr = new QueryRunner(dataSource);
String Sql = "select * from users";
List<users> list = qr.query(Sql,new BeanListHandler<users>(users.class));
for(users u : list){
System.out.println(u.uname);
}
