Java-Jdbc基础

JDBC

概念

JDBC(Java DataBase Connectivity(Java数据库的连接)):

是一种用于执行SQL语句(DML,DDL,DQL)的Java API,可以为多种关系数据库(oracle,mysql,SQL server)提供统一访问,它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能够编写数据库应用程序.

不管使用哪一种数据库,都需要去导入这个数据库给我们提供的jdbc实现的jar包

访问数据库的两种形式

1.JDBC最原生的方式去访问

2.使用第三方框架来进行访问(底层依然是JDBC)

JDBC连接数据库

导入相应的jar包

​ jdbc连接数据库步骤:贾琏欲执事

​ 1.加载驱动

​ 2.建立连接

​ 3.获取语句

​ 4.执行语句

​ 5.关闭连接,释放资源

加载驱动三种方式

这里介绍最常用的一种:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
try {
//第一步:通过反射加载驱动
Class.forName("com.mysql.jdbc.Driver");
//第二步:建立连接
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbcdemo","root","594395fzk");
//第三步:获取语句对象,建表,书写sql语句
Statement statement = conn.createStatement();
String sql = "create table student(id bigint(20),name varchar(255),age int(10))";
//第四步:执行sql语句
//boolean b = statement.execute(sql);
String ins = "insert into student (id,name,age) value ('02','电棍','2')";
statement.executeUpdate(ins);
//第五步:释放资源,关闭数据库连接
statement.close();
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

至于其他两种:😝😝😝😝😝百度一下,你就知道

使用JDBC实现增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
package com.ifueen.classtest.jdbcdemo;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class JdbcConnect {
Connection conn;
Statement statement;
@Before
public void before() {
try {
//加载启动
Class.forName("com.mysql.jdbc.Driver");
//连接数据库
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbcdemo","root","594395fzk");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @throws Exception
* 添加数据
*/
@Test
public void insert() throws Exception {
String ins = "insert into student (id,name,age) values (5,'窦唯',20)";
statement = conn.createStatement();
statement.execute(ins);
}
/**
* @throws Exception
* 删除数据
*/
@Test
public void delete() throws Exception {
String del = "delete from student where id=5";
statement = conn.createStatement();
statement.execute(del);
}
/**
* @throws Exception
* 更新数据
*/
@Test
public void update() throws Exception {
String update = "update student set name='小小山泥若' where id=1";
statement = conn.createStatement();
statement.executeUpdate(update);
}
/**
* @throws Exception
* 查询数据
*/
@Test
public void select() throws Exception {
String select = "select * from student";
statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(select);
while(resultSet.next()){
System.out.println(resultSet.getString("name"));
}
}
/**
* @throws SQLException
* 关闭连接,释放资源
*/
@After
public void after() throws SQLException{
statement.close();
conn.close();
}

}

封装JDBC工具类

为了更方便,让代码使用起来更高效,可以将连接数据库的操作封装成一个工具类,以后在每次要进行数据库连接的时候直接使用工具类即可

这里先将数据库的驱动连接,url,账户密码等放在了资源文件夹的properties文件中

资源文件db.properties:

1
2
3
4
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/jdbcwork
username=root
password=594395fzk

封装的工具类:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
package com.ifueen.classtest.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {
//创建单例模式
private static JDBCUtil instance;
private JDBCUtil() {}
public static JDBCUtil getinstance() {
return instance;
}
private static Properties prop = new Properties();
//静态代码块,存放要优先加载的功能语句
static{
try {
//通过字节码来获取流
prop.load(JDBCUtil.class.getResourceAsStream("/db.properties"));
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
instance = new JDBCUtil();
try {
//加载驱动
Class.forName(prop.getProperty("driverClassName"));
} catch (Exception e) {
e.printStackTrace();
}
}

/**
* @return
* 连接数据库的方法
*/
public Connection getconn() {
Connection conn = null;
try {
//连接数据库
conn = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}

/**
*
* 关闭连接,释放资源
*/
public void getclose(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {

try {
if (st != null)
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {

try {
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public void getclose(Statement st, Connection conn) {
try {
if(st!=null)
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

这下连接数据库的操作就很好地封装成了一个工具类,只需要在要进行操作的地方使用即可

测试类:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.ifueen.classtest.util;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class UtilTest {

@Test
public void test() throws Exception {
//调用封装好的加载驱动的方法
Connection conn = JDBCUtil.getinstance().getconn();
Statement st = conn.createStatement();
String sql = "select * from student";
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("name"));
}
//调用关闭驱动的方法
JDBCUtil.getinstance().getclose(rs, st, conn);
}

}

Dao层的实现

DAO : 主要是用来做数据库的操作
模块化,分工明确,解耦(责任分离)

刚才已经说到要分层,各层之间相互调用,现在数据层 调用 数据库,需要传什么内容?
比如 要添加一个用户,怎么添加?

界面层:收集数据; name,password,age….等等 需要传递很多东西;
业务层:界面层收集之后,传到业务层;

因为我们没有前端传过来的数据,现在只使用dao,domian层来进行crud操作

大体结构如下

首先肯定要在数据库中将表建好,然后写一个javabean类和这张表一一对应

domian层下面的javabean类:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package com.ifueen.homework.domian;

public class Student {
private long id;
private String username;
private String password;
private int age;
private boolean sex;
private String intro;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public boolean isSex() {
return sex;
}
public void setSex(boolean sex) {
this.sex = sex;
}
public String getIntro() {
return intro;
}
public void setIntro(String intro) {
this.intro = intro;
}
public Student(long id, String username, String password, int age, boolean sex, String intro) {
super();
this.id = id;
this.username = username;
this.password = password;
this.age = age;
this.sex = sex;
this.intro = intro;
}
public Student(Long id,String username) {
super();
this.username = username;
this.id = id;
}
public Student() {
super();
}

}

Dao类:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package com.ifueen.homework.dao;

import com.ifueen.homework.domian.Student;

public interface StudentDao {
//增加数据
void add(Student s);
//删除数据
void delete(long id);
//修改数据
void update(Student s);
//查找数据
Student select(Student s);
//查找数据2
void select(Long id);
}

dao的实现类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
package com.ifueen.homework.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.ifueen.classtest.util.JDBCUtil;
import com.ifueen.homework.dao.StudentDao;
import com.ifueen.homework.domian.Student;
import com.sun.javafx.binding.Logging;

public class StudentDaoImpl implements StudentDao{

/**
* 新增数据
*/
@Override
public void add(Student s) {
// TODO Auto-generated method stub
try {
//加载驱动,连接数据库
Connection conn = JDBCUtil.getinstance().getconn();
//得到Statement对象
Statement st = conn.createStatement();
//写sql语句
String sql = "insert into student(id,username,password,age,sex,intro) "
+ "values ('"+s.getId()+"','"+s.getUsername()+"','"+s.getPassword()+"','"+s.getAge()+"','"+s.isSex()+"','"+s.getIntro()+"')";
//执行sql语句
st.execute(sql);
//关闭连接,释放资源
JDBCUtil.getinstance().getclose(st, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**
* 删除数据
*/
@Override
public void delete(long id) {
// TODO Auto-generated method stub
try {
Connection conn = JDBCUtil.getinstance().getconn();
Statement st = conn.createStatement();
String sql = "delete from student where id="+id;
st.execute(sql);
JDBCUtil.getinstance().getclose(st, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**
* 修改数据
*/
@Override
public void update(Student s) {
// TODO Auto-generated method stub
try {
Connection conn = JDBCUtil.getinstance().getconn();
Statement st = conn.createStatement();
String sql = "update student set username='"+s.getUsername()+"' where id="+s.getId();
st.executeUpdate(sql);
JDBCUtil.getinstance().getclose(st, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**
* 查询数据1
*/
@Override
public Student select(Student s) {
Student student = null;
// TODO Auto-generated method stub
try {
Connection conn = JDBCUtil.getinstance().getconn();
Statement st = conn.createStatement();
String sql = "select * from student";
ResultSet set = st.executeQuery(sql);
student = new Student();
while (set.next()) {
student.setId(set.getLong("id"));
student.setUsername(set.getString("username"));
student.setPassword(set.getString("password"));
student.setAge(set.getInt("age"));
student.setSex(set.getBoolean("sex"));
student.setIntro(set.getString("intro"));
}
JDBCUtil.getinstance().getclose(st, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student;
}

/**
* 查询数据2
* 全部循环打印显示出来
*/
@Override
public void select(Long id) {
Student student = null;
// TODO Auto-generated method stub
try {
Connection conn = JDBCUtil.getinstance().getconn();
Statement st = conn.createStatement();
String sql = "select * from student where id="+id;
ResultSet set = st.executeQuery(sql);
student = new Student();
while (set.next()) {
System.out.print(set.getLong("id")+" ");
System.out.print(set.getString("username")+" ");
System.out.print(set.getString("password")+" ");
System.out.print(set.getInt("age")+" ");
System.out.print(set.getBoolean("sex")+" ");
System.out.print(set.getString("intro")+" ");
}
JDBCUtil.getinstance().getclose(st, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}

最后测试类:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
package com.ifueen.homework.test;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Before;
import org.junit.Test;

import com.ifueen.classtest.util.JDBCUtil;
import com.ifueen.homework.dao.StudentDao;
import com.ifueen.homework.dao.impl.StudentDaoImpl;
import com.ifueen.homework.domian.Student;

public class StudentTest {
StudentDao std = new StudentDaoImpl();

/**
* 添加数据
*/
@Test
public void add() {
Student student = new Student(1,"山泥若","123456",25,true,"我顶不住了");
std.add(student);
}
/**
* 删除数据
*/
@Test
public void delete() {
std.delete(1L);
}
/**
* 修改数据
*/
@Test
public void update() {
Student student = new Student(2L,"落日飞车");
std.update(student);
}
/**
* 查询数据
*/
@Test
public void select() {
Student student = new Student(1,"山泥若","123456",25,true,"我顶不住了");
//拿到查询的结果
Student select = std.select(student);
System.out.println(select.getUsername());
System.out.println(select.getIntro());
}
/**
* 查询数据2
*/
@Test
public void selects() {
std.select(1L);
}

}
❤赏点钱让我买杯快乐水8❤