JDBC准备工作
-
下载 mysql-connector-java-8.0.25.jar
官网下载地址:https://dev.mysql.com/downloads/connector/j/ 下载mysql对应版本的
-
创建项目,在项目下创建lib文件夹,把 mysql-connector-java-8.0.25.jar添加到lib文件夹中,然后把mysql-connector-java-8.0.25.jar添加到Library库中
-
然后在src中正常创建包,就可以写JDBC 有关代码了
可以在mysql中查看连接数量:
可以让程序先睡几秒,然后mysql客户端执行 show processlist;
JDBC简单操作
-
查询操作
try { // 加载驱动 Class.forName("com.mysql.jdbc.Driver"); // 连接数据 String url = "jdbc:mysql://localhost:3306/book"; String user = "root"; String password = "034312"; Connection conn = DriverManager.getConnection(url,user,password); // 执行语句 String sql = "select * from books"; Statement st = conn.createStatement(); ResultSet res = st.executeQuery(sql); while(res.next()){ System.out.println(res.getString("bookname")); System.out.println(res.getString("price")); } } catch (Exception e) { e.printStackTrace(); }finally{ // 释放资源 }
-
增加操作
try { // 加载驱动 Class.forName("com.mysql.jdbc.Driver"); // 连接数据 String url = "jdbc:mysql://localhost:3306/book"; String user = "root"; String password = "034312"; Connection conn = DriverManager.getConnection(url,user,password); // 执行语句 String sql = "insert into books (bookname,price) values ('查拉图斯特拉如是说',1000)"; Statement st = conn.createStatement(); int i = st.executeUpdate(sql); System.out.println(i); } catch (Exception e) { e.printStackTrace(); } finally{ // 释放资源 }
只有 DQL (数据库查询语言)使用 :conn.createStatement()
DML(数据库操作语言),DDL(数据库定义语言),DCL(数据库控制语言)使用 :st.executeUpdate(sql);
Dao设计思想
-
什么是DAO?
Database Access Object 数据库存取对象,用于业务逻辑层与持久化数据之间,实现持久化数据的访问
-
什么是ORM?
Object Relation Mapping 对象关系映射,将关系库中表中的记录映射为对象,字段映射为对象的属性,因此ORM的目的是为了方便开发人员以面向对象的思想对数据库的操作
-
什么是domain?
就是一个类,又叫javaBean ,他的规范为:一个类中有setter 和 getter方法
目的:是为了存储数据库记录,更好的方便与DAO操作,使用户层与数据库交互的核心中转站
-
DAO设计规范
-
编写DAO组件:定义DAO接口 和 编写相应的DAO实现类
-
为什么要定义接口?
面向接口编程,可以实现不同类型的数据库的操作
例如: 数据库使用Oracle ,Redis,Mysql 只需要实现该接口即可
-
-
包名的规范
-
整体规范
域名倒写.模块名称.组件名称
-
DAO包的规范
- com.zhj.jdbc.domain:存储所有的domain
- com.zhj.jdbc.dao:存储所有的dao接口
- com.zhj.jdbc.dao.impl:存储dao接口的实现类
- cmo.zhj.jdbc.dao.test:存储dao组件的测试类
-
类名规范
- domain类:存储与domain包中,见名知意
- dao接口:存储于dao包中,规范 IDomainDao
- dao实现类:存储于impl包中,规范 DomainDaoImpl
-
-
-
示例
Student
package com.zhj.jdbc.domain;
public class Student {
private String name;
private Integer age;
public Student() {
}
public Student(String name, Integer age) {
this.name = name;
this.age = age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Student{" +
"name='" + name + '\'' +
", age=" + age +
'}';
}
}
Dao
package com.zhj.jdbc.dao;
import com.zhj.jdbc.domain.Student;
import java.util.List;
public interface Dao {
// 增
public abstract void add(Student stu);
// 删
public abstract void delete(int id);
// 改
public abstract void set(int id,Student stu);
// 查
public abstract Student get(int id);
// 查询所有
public abstract List getAll();
}
StudentDaoImpl
package com.zhj.jdbc.dao.impl;
import com.zhj.jdbc.dao.Dao;
import com.zhj.jdbc.domain.Student;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StudentDaoImpl implements Dao {
@Override
public void add(Student stu) {
Statement st = null;
Connection conn = null;
try {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 建立连接
String url = "jdbc:mysql://localhost:3306/stus";
String user = "root";
String password = "034312";
conn = DriverManager.getConnection(url, user, password);
// 创建语句
String sql = "insert into student(name,age) values('" + stu.getName() + "'," + stu.getAge() + ")";
// 执行语句
st = conn.createStatement();
st.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
try {
if(st != null){
st.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
@Override
public void delete(int id) {
Statement st = null;
Connection conn = null;
try {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 建立连接
String url = "jdbc:mysql://localhost:3306/stus";
String user = "root";
String password = "034312";
conn = DriverManager.getConnection(url, user, password);
// 创建语句
String sql = "delete from student where id = "+id;
// 执行语句
st = conn.createStatement();
st.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
try {
if(st != null){
st.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
@Override
public void set(int id, Student stu) {
Statement st = null;
Connection conn = null;
try {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 建立连接
String url = "jdbc:mysql://localhost:3306/stus";
String user = "root";
String password = "034312";
conn = DriverManager.getConnection(url, user, password);
// 创建语句
String sql = "update student set name = '"+stu.getName()+"',age = "+stu.getAge()+" where id = "+id;
// 执行语句
st = conn.createStatement();
st.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
try {
if(st != null){
st.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
@Override
public Student get(int id) {
Statement st = null;
Connection conn = null;
try {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 建立连接
String url = "jdbc:mysql://localhost:3306/stus";
String user = "root";
String password = "034312";
conn = DriverManager.getConnection(url, user, password);
// 创建语句
String sql = "select * from student where id = "+id;
// 执行语句
st = conn.createStatement();
ResultSet res = st.executeQuery(sql);
//
if(res.next()){
String name = res.getString("name");
Integer age = res.getInt("age");
Student student = new Student(name,age);
return student;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
try {
if(st != null){
st.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
@Override
public List getAll() {
Statement st = null;
Connection conn = null;
try {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 建立连接
String url = "jdbc:mysql://localhost:3306/stus";
String user = "root";
String password = "034312";
conn = DriverManager.getConnection(url, user, password);
// 创建语句
String sql = "select * from student";
// 执行语句
st = conn.createStatement();
ResultSet res = st.executeQuery(sql);
// 返回集合
List<Student> stus = new ArrayList<>();
while(res.next()){
String name = res.getString("name");
Integer age = res.getInt("age");
Student student = new Student(name,age);
stus.add(student);
}
return stus;
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
try {
if(st != null){
st.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
TestMain
package com.zhj.jdbc.test;
import com.zhj.jdbc.dao.impl.StudentDaoImpl;
import com.zhj.jdbc.domain.Student;
import org.junit.Test;
public class TestMain {
@Test
public void addTest() {
// Student stu = new Student("八戒",33);
// StudentDaoImpl sdi = new StudentDaoImpl();
// sdi.add(stu);
}
@Test
public void deleteTest() {
// StudentDaoImpl sdi = new StudentDaoImpl();
// sdi.delete(5);
}
@Test
public void setTest() {
// StudentDaoImpl sdi = new StudentDaoImpl();
// Student student = new Student("八戒",33);
// sdi.set(7,student);
}
@Test
public void getTest() {
// StudentDaoImpl sdi = new StudentDaoImpl();
// System.out.println(sdi.get(2));
}
@Test
public void getAllTest() {
StudentDaoImpl sdi = new StudentDaoImpl();
System.out.println(sdi.getAll());
}
}
- 代码问题:
每个DAO方法中都会有驱动名称,url,用户名,密码,每个DAO都有相同的4行代码
驱动使用静态代码块,提取公共工具类
每个DAO方法操作都需要connection对象,至于是怎么创建的不关心
提取到公共工具类中
每次DAO都要关闭资源
提取到公共工具类中
DAO方法中拼接SQL太麻烦
接口: Statement
作用: 用于java程序与数据库之间进行数据传输
三个实现子类, Statement:直接使用静态的sql语句 PrepareStatement: 预编译模版
内部优化
支持优化情况:Mysql不支持,Oracle支持
更安全: 可以防止sql注入
CallableStatement:
这三个流都需要关闭
代码优化
DaoUtil
package com.zhj.jdbc.dao.daoutil;
import java.sql.*;
public class DaoUtil {
public static String url = "jdbc:mysql://localhost:3306/stus";
public static String user = "root";
public static String password = "034312";
public static void close(Statement st, Connection conn, ResultSet res){
// 释放资源
try {
if(st != null){
st.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(res != null){
res.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
StudentDaoImpl
package com.zhj.jdbc.dao.impl;
import com.zhj.jdbc.dao.Dao;
import com.zhj.jdbc.dao.daoutil.DaoUtil;
import com.zhj.jdbc.domain.Student;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StudentDaoImpl implements Dao {
private Statement st = null;
private PreparedStatement ps = null;
private Connection conn = null;
private ResultSet res = null;
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
@Override
public void add(Student stu) {
try {
// 加载驱动
// 建立连接
conn = DriverManager.getConnection(DaoUtil.url, DaoUtil.user, DaoUtil.password);
// 创建语句
// String sql = "insert into student(name,age) values('" + stu.getName() + "'," + stu.getAge() + ")";
String sql = "insert into student(name,age) values(?,?)";
// 使用预编译
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,stu.getName());
ps.setInt(2,stu.getAge());
// 执行语句
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
DaoUtil.close(ps, conn, res);
}
}
@Override
public void delete(int id) {
try {
conn = DriverManager.getConnection(DaoUtil.url, DaoUtil.user, DaoUtil.password);
// 创建语句
// String sql = "delete from student where id = " + id;
String sql = "delete from student where id = ?";
// 执行语句
ps = conn.prepareStatement(sql);
ps.setInt(1,id);
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
DaoUtil.close(ps, conn, res);
}
}
@Override
public void set(int id, Student stu) {
try {
// 加载驱动
conn = DriverManager.getConnection(DaoUtil.url, DaoUtil.user, DaoUtil.password);
// 创建语句
String sql = "update student set name = ?,age = ? where id = ?";
// 执行语句
// st = conn.createStatement();
// st.executeUpdate(sql);
ps = conn.prepareStatement(sql);
ps.setString(1, stu.getName());
ps.setInt(2,stu.getAge());
ps.setInt(3,id);
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
DaoUtil.close(ps, conn, res);
}
}
@Override
public Student get(int id) {
try {
conn = DriverManager.getConnection(DaoUtil.url, DaoUtil.user, DaoUtil.password);
// 创建语句
String sql = "select * from student where id = ?";
// 执行语句
// st = conn.createStatement();
// res = st.executeQuery(sql);
this.ps = conn.prepareStatement(sql);
this.ps.setInt(1,id);
this.res = ps.executeQuery();
//
if (this.res.next()) {
String name = this.res.getString("name");
Integer age = this.res.getInt("age");
Student student = new Student(name, age);
return student;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
DaoUtil.close(st, conn, res);
}
return null;
}
@Override
public List getAll() {
try {
conn = DriverManager.getConnection(DaoUtil.url, DaoUtil.user, DaoUtil.password);
// 创建语句
String sql = "select * from student";
// 执行语句
st = conn.createStatement();
res = st.executeQuery(sql);
// 返回集合
List<Student> stus = new ArrayList<>();
while (res.next()) {
String name = res.getString("name");
Integer age = res.getInt("age");
Student student = new Student(name, age);
stus.add(student);
}
return stus;
} catch (Exception e) {
e.printStackTrace();
} finally {
DaoUtil.close(st, conn, res);
}
return null;
}
}