JDBC
作用: 连接数据库,完成java与数据库之间的数据交互。
1.纯粹的使用jdbc流程完成数据交互
加载数据库的连接驱动
例如:com.mysql.jdbc.Driver
Class.forName("com.mysql.jdbc.Driver")
获取Connection连接对象
Connection con = DriverManager.getConnection(url,username,password);
获取Statement执行对象
Statement:发送sql并且执行
- PreparedStatement
- CallableStatement
获取执行结果
增删改: 受影响函数
查询: 结果集(resultSet)
处理结果集
next(): 行的遍历
getXXX(String colName) :根据列名称取XX类型的值
getXXX(int colIndex) :根据索引取XX类型的值。
getObject(String colName): 根据列明取Object类型的值。
释放资源
io,sql资源不会被垃圾回收器回收
2.dbutil工具(封装Jdbc)
构造方法:
QueryRunner(DataSource dataSource)
QueryRunner()
方法:
T query(String sql, ResultSetHandler<T> handler,Object...params)
T query(String sql, Conenction con ,ResultSetHandler<T> handler,Object...params)
int update(String sql,Object...params)
int update(String sql,Connection con,Object...params)
3.连接池的思想和原理
c3p0连接池 : ComboPooledDataSource
连接池参数理解:
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
4.数据库事务问题
事务特点:
*隔离性
A事务的成功或者是失败,对其他事务B事务没有用任何影响。
*原子性
在一个事务中,所有的sql执行要么同时成功,要么同时失败。
*一致性
在一条事务中,所有数据在改变前和改变后的总数据是一致。
*持久层
事务对数据库的修改时永久性,一旦事务执行成功数据永久改变。
事务隔离级别:
脏读、不可重复读和幻读
事务开始:BEGIN
事务提交: COMMINT
事务回滚: RALLBACK
注意:SQL异常一定不能在DAO层处理,影响事务的回滚。
package com.fs.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
String url = "jdbc:mysql://localhost:3306/esa?useUnicode=true&characterEncoding=utf-8";
String username = "root";
String password = "12345678";
Connection conn = DriverManager.getConnection(url,username,password);
//定义sql
String sql = "update emp set age = 100 where id = 1";
//获取执行sql的对象
Statement stmt = conn.createStatement();
//执行sql
int count = stmt.executeUpdate(sql);//受影响的行数
//处理结果
System.out.println(count);
//释放资源
stmt.close();
conn.close();
}
}
package com.fs.dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class GoodDao {
private static Connection con;
//静态代码块: 类加载时
static{
//1.加载JDBC驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException throwables) {
throwables.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost:3306/" +
"bookStore?useUnicode=true&characterEncoding=utf8&useSSL=false",
"root","12345678");
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Book book = new Book();
book.setBid("9");
book.setImage("book_img/9317290-1_1.jpg");
book.setBname("软件工程");
book.setPrice(90.5);
book.setAuthor("XXX");
book.setCid("2");
/*List<Book> bookList = findAllBook();
for(Book book1: bookList){
System.out.println(book1);
}*/
addBook(book);
bookDelete(9);
updateBook();
List<Book> bookList = findBookByLikeName("数据库");
for(Book book1: bookList){
System.out.println(book1);
}
}
//根据ID,删除图书
public static void bookDelete(int bid) throws ClassNotFoundException, SQLException {
//3.获取Statement对象
Connection con = getConnection();
/*Statement stm = con.createStatement();
String sql = "DELETE FROM `t_book` WHERE `bid` = " + bid;
//4.发送sql语句,并且执行sql语句
int num = stm.executeUpdate(sql);*/
String sql = "DELETE FROM `t_book` WHERE `bid` =?";
PreparedStatement pstm = con.prepareStatement(sql);
pstm.setObject(1,8);
int rs = pstm.executeUpdate();
//5.处理结果
if(rs > 0 ){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
//6.jdbc对象不会被垃圾回收器回收,释放内存资源
if(pstm != null){
pstm.close();
}
if(con != null){
con.close();
}
}
//添加图书
public static void addBook(Book book) throws ClassNotFoundException, SQLException {
Connection con = getConnection();
//Statement stm = con.createStatement();
String sql = "INSERT INTO `t_book` VALUES(?,?,?,?,?,?)";
PreparedStatement pstm = con.prepareStatement(sql);
pstm.setObject(1,book.getBid());
pstm.setObject(2,book.getBname());
pstm.setObject(3,book.getPrice());
pstm.setObject(4,book.getAuthor());
pstm.setObject(5,book.getImage());
pstm.setObject(6,book.getCid());
int rs = pstm.executeUpdate();
// List<Book> bookList = new ArrayList<>();
/*StringBuffer sb = new StringBuffer("INSERT INTO `t_book` VALUES(");
sb.append(""+book.getBid()+",");
sb.append("'"+book.getBname()+"',");
sb.append(""+book.getPrice()+",");
sb.append("'"+book.getAuthor()+"',");
sb.append("'"+book.getImage()+"',");
sb.append("'"+book.getCid()+"')");
int num = stm.executeUpdate(sb.toString());*/
if(rs >0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
if(pstm != null){
pstm.close();
}
if(con != null){
con.close();
}
}
public static void updateBook() throws ClassNotFoundException, SQLException{
Connection con = getConnection();
/*Statement stm = con.createStatement();
StringBuffer sb = new StringBuffer("UPDATE `t_book` SET ");
sb.append(""+book.getBid()+",");
sb.append("'"+book.getBname()+"',");
sb.append(""+book.getPrice()+",");
sb.append("'"+book.getAuthor()+"',");
sb.append("'"+book.getImage()+"',");
sb.append(""+book.getCid()+",");*/
String sql = "UPDATE `t_book` SET bname = ? where bid = ?";
PreparedStatement pstm = con.prepareStatement(sql);
pstm.setObject(1,"java基础");
pstm.setObject(2,22);
int rs = pstm.executeUpdate();
//int num = pstm.executeUpdate(sb.toString());
if(rs > 0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
if(pstm != null){
pstm.close();
}
if(con != null){
con.close();
}
}
public static List<Book> findAllBook() throws ClassNotFoundException, SQLException{
Connection con = getConnection();
Statement stm = con.createStatement();
String sql = "SELECT * FROM `t_book`";
ResultSet rs = stm.executeQuery(sql);
List<Book> bookList = new ArrayList<>();
while(rs.next()){
Book book = new Book();
//结果集每一行数据 转换为 商品对象数据
book.setBid(rs.getString("bid"));
//Object rs.getObject("id");
book.setBname(rs.getString("bname"));
book.setPrice(rs.getDouble("price"));
book.setAuthor(rs.getString("author"));
book.setImage(rs.getString("image"));
book.setCid(rs.getString("cid"));
bookList.add(book);
}
if(rs != null){
rs.close();
}
if(stm != null){
stm.close();
}
if(con != null){
con.close();
}
return bookList;
}
public static Book findBookById(int bid) throws ClassNotFoundException, SQLException{
Connection con = getConnection();
Statement stm = con.createStatement();
String sql = "SELECT * FROM `book` WHERE `bid` = " + bid;
ResultSet rs = stm.executeQuery(sql);
Book book = null;
while(rs.next()){
book = new Book();
//结果集每一行数据 转换为 商品对象数据
book.setBid(rs.getString("bid"));
//Object rs.getObject("id");
book.setBname(rs.getString("bname"));
book.setPrice(rs.getDouble("price"));
book.setAuthor(rs.getString("author"));
book.setImage(rs.getString("image"));
book.setCid(rs.getString("cid"));
}
if(rs != null){
rs.close();
}
if(stm != null){
stm.close();
}
if(con != null){
con.close();
}
return book;
}
//根据图书名称模糊查询
public static List<Book> findBookByLikeName(String bname) throws ClassNotFoundException, SQLException {
Connection con = getConnection();
/* Statement stm = con.createStatement();
String sql = "SELECT * FROM `t_book` WHERE `bname` like %" + bname +"%";
ResultSet rs = stm.executeQuery(sql);*/
String sql = "SELECT * FROM `t_book` WHERE `bname` like ?";
PreparedStatement pstm = con.prepareStatement(sql);
pstm.setObject(1,'%'+bname+'%');
ResultSet rs = pstm.executeQuery();
List<Book> bookList = new ArrayList<>();
while(rs.next()){
Book book = new Book();
//结果集每一行数据 转换为 商品对象数据
book.setBid(rs.getString("bid"));
//Object rs.getObject("id");
book.setBname(rs.getString("bname"));
book.setPrice(rs.getDouble("price"));
book.setAuthor(rs.getString("author"));
book.setImage(rs.getString("image"));
book.setCid(rs.getString("cid"));
bookList.add(book);
}
if(rs != null){
rs.close();
}
if(pstm != null){
pstm.close();
}
if(con != null){
con.close();
}
return bookList;
}
}