这里写目录标题
一、JDBC连接流程
- 下载相应的jar包或者使用Maven注入依赖
- 注册加载一个驱动
- 创建数据库连接(Connection)
- 构造SQL语句
- 创建statement,发送sql语句
- 执行sql语句
- 处理sql结果
- 关闭statement和connection
二、相应的依赖以及数据库准备
1.数据库的创建
#可以将本代码放入一个create,sql文件,执行下面的操作。(sql脚本的执行)
# 创建数据库
create database managelibrary;
user managelibrary;
# 创建表
create table if not exists `user`(
`id` int unsigned auto_increment,
`name` varchar(11) not null,
`sid` varchar(11) not null,
`num` varchar(11) not null,
`password` varchar(11) not null,
`telphone` varchar(11),
primary key(`id`)
)engine=InnoDB Default charset=utf8;
使用 \. D:create.sql 或者 source \D:create.sql 可以直接创建
2.maven依赖
<!--mysql包括c3p0和mysql的java连接器-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
三、具体语句
- 注册加载驱动
Class.forName("com.mysql.jdbc.Driver"); //使用mysql的驱动;
- 创建一个连接
Connection connection = DriverManager.getConnection("jdbc://ip地址:端口号/数据库名称“,“用户名”,"密码");
- 构造sql语句 基本sql语句
例如
//插入
insert into user(name, sid, password, telphone, num) values(?,?,?,?,?);//问号表示位置参数
//删除
delete from user where name = '';
//查找
select * from user where name = '';
- prepareStatement的准备
- 执行
//执行查找
stmt.executeQuery();
//执行更新
stmt.executeUpdate();
6.处理查找结果
ResultSet resultset = statement.executeQuery();
四、实例
- 实体文件
基本属性 + 构造器 + set,get + toString方法
private int id;
private String name;
private String password;
private String Sid;
private String telphone;
private String num;
- 配置文件 jdbc.properties
需要放在classpath路径下
driver=com.mysql.jdbc.Driver #如果版本较高可以使用com.cj.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/managelibrary?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
#这里指定了字符编码和解码格式,时区,是否加密传输
username=root
password=roto
- JdbcUtil文件
//如果从properties中获取配置信息可以这么写,第一次尝试可以直接下面的写法
private Properties properties;
public Connection getCon() throws ClassNotFoundException, SQLException, IOException {
properties = new Properties();
InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");//获取配置文件中的内容
properties.load(in);
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url,username , password);
return connection;
}
public class JdbcUtil {
// 1.驱动获取 2.连接数据库
public Connection getCon() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ManageLibrary", "root", "1230");
return connection;
}
//根据姓名查找
public ResultSet findByName(String username) throws SQLException, ClassNotFoundException {
String sql = "select * from user where name = ?"; //3.构造查找的sql
Connection connection = getCon();
PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
statement.setString(1, username); //4.statement的准备
ResultSet resultset = statement.executeQuery();//5.执行sql的语句
return resultset;
}
//插入用户
public boolean insertUser(user user) throws ClassNotFoundException, SQLException {
Connection connection =getCon();
String sql = "insert into user(name, sid, password, telphone, num) values(?,?,?,?,?)";
PreparedStatement psmt = (PreparedStatement) connection.prepareStatement(sql);
psmt.setString(1, user.getName());
psmt.setString(2, user.getSid());
psmt.setString(3,user.getPassword());
psmt.setString(4, user.getTelphone());
psmt.setInt(5, user.getNum());
psmt.executeUpdate();
//用来判断是否插入成功,成功可以从数据库中找到
ResultSet resultSet = findByName(user.getName());
boolean flag = false;
while(resultSet.next()) {
flag = true;
}
return flag;
}
//判断用户名和密码是否匹配,这个应该放在service层
public boolean checkUser(String username,String password) throws Exception {
Connection connection = getCon();
String sql = "select * from user where name = ? and password = ?";
PreparedStatement psmt = connection.prepareStatement(sql);
psmt.setString(1, username);
psmt.setString(2, password);
ResultSet resultSet = psmt.executeQuery();
boolean flag = false;
while(resultSet.next()) {
flag = true;
}
return flag;
}
//根据姓名进行删除
public boolean delete(String username,String Sid) throws SQLException, ClassNotFoundException {
Connection connection = getCon();
String sql = "delete from user where name = ?";
PreparedStatement pmst = connection.prepareStatement(sql);
pmst.setString(1, username);
pmst.executeUpdate();
//判断删除是否成功
ResultSet resultSet = findByName(username);
boolean flag = true;
while(resultSet.next()) {
flag = false;
}
return flag;
}
}
- 测试文件
public class Main {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//testInser();
//testDelete();
testFindByName("kk");
}
//检测插入
static void testInser() throws ClassNotFoundException, SQLException {
JdbcUtil jdbcdemo = new JdbcUtil();
user user = new user("","","","","");
if(jdbcdemo.insertUser(user)) {
System.out.println("插入成功");
}
else System.out.println("无效注册");
}
//检测根据姓名查找
static void testFindByName(String name) throws SQLException, ClassNotFoundException {
JdbcUtil jdbcUtil = new JdbcUtil();
ResultSet resultSet = jdbcUtil.findByName(name);
boolean flag = true;
while(resultSet.next()){
flag = false;
System.out.println("查找信息如下");
user user2 = new user(resultSet.getInt(1),
resultSet.getString(2),
resultSet.getString(3),
resultSet.getString(4),
resultSet.getString(5),
resultSet.getString(6));
System.out.println(user2.toString());
}
if(flag) System.out.println("没有此人!");
}
//检测姓名和密码是否对应
static void testCheck() throws Exception {
JdbcUtil jdbcUtil = new JdbcUtil();
if(jdbcUtil.checkUser("zk", "1234")) {
testFindByName("zk");
}
else System.out.println("账号或密码错误");
}
//检测删除
static void testDelete() throws ClassNotFoundException, SQLException {
JdbcUtil jdbcUtil = new JdbcUtil();
String[] username = {""};
String[] userpassword = {""};
for(int i = 0;i < username.length;i++) {
if(jdbcUtil.delete(username[i],userpassword[i])){
System.out.println("成功删除" + username[i]);
}
else {
System.out.println( username[i]+"删除失败");
}
}
}
}
五、具体类的总结
具体类 | 作用 |
---|---|
DriverManager | 驱动管理器,用于注册驱动,是获取Connection对象的入口 |
Driver | 数据库驱动,用于获取Connection对象 |
Statement | sql执行器,用于执行sql |
ResultSet | 结果集,用于封装和操作查询结果 |
prepareCall | 用于调用存储过程 |
1. PreparedStatement
2. statement
3.Connector
4.Driver
5.ResultSet
六、优化样例代码
1.优化之后可以避免connect的关闭忘记。
2.JbcbUtil设计成静态的使用了单例模式。
3.应该考虑多线程的下的单例问题。
1.jdbcUtil
//使用单例模式进行设计JDBC,没有考虑是否多线程问题
//设计成静态的链接可以避免close的问题
public class JdbcUtil {
static private Properties properties;
static public Connection getConnection() throws ClassNotFoundException, SQLException {
properties = new Properties();
InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");//获取配置文件中的内容
try{
properties.load(in);
}catch (Exception e){
e.printStackTrace();
}
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url,username , password);
return connection;
}
}
2.userDao
public class UserDao {
private Connection connection;
public UserDao(Connection connection){
this.connection = connection;
}
public ResultSet findByName(String username) throws SQLException, ClassNotFoundException {
String sql = "select * from user where name = ?";
PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
statement.setString(1, username);
ResultSet resultset = statement.executeQuery();
return resultset;
}
public boolean insertUser(user user) throws ClassNotFoundException, SQLException {
String sql = "insert into user(name, sid, password, telphone, num) values(?,?,?,?,?)";
PreparedStatement psmt = (PreparedStatement) connection.prepareStatement(sql);
psmt.setString(1, user.getName());
psmt.setString(2, user.getSid());
psmt.setString(3,user.getPassword());
psmt.setString(4, user.getTelphone());
psmt.setString(5, user.getNum());
psmt.executeUpdate();
ResultSet resultSet = findByName(user.getName());
boolean flag = false;
while(resultSet.next()) {
flag = true;
}
return flag;
}
public boolean checkUser(String username,String password) throws Exception {
String sql = "select * from user where name = ? and password = ?";
PreparedStatement psmt = connection.prepareStatement(sql);
psmt.setString(1, username);
psmt.setString(2, password);
ResultSet resultSet = psmt.executeQuery();
connection.close();
boolean flag = false;
while(resultSet.next()) {
flag = true;
}
return flag;
}
public boolean delete(String username,String Sid) throws SQLException, ClassNotFoundException {
String sql = "delete from user where name = ?";
PreparedStatement pmst = connection.prepareStatement(sql);
pmst.setString(1, username);
pmst.executeUpdate();
ResultSet resultSet = findByName(username);
boolean flag = true;
while(resultSet.next()) {
flag = false;
}
return flag;
}
}
3.Main
public class Main {
static private UserDao userDao; //包含了dao和service层的部分实现
public static void main(String[] args) throws ClassNotFoundException, SQLException {
userDao = new UserDao(JdbcUtil.getConnection()); //开启连接
testInser();
testFindByName("zk");
testDelete();
}
//检测插入
static void testInser() throws ClassNotFoundException, SQLException {
user user = new user("zk","1525475","2017","17861401461","46");
if(userDao.insertUser(user)) {
System.out.println("插入成功");
}
else System.out.println("无效注册");
}
//检测根据姓名查找
static void testFindByName(String name) throws SQLException, ClassNotFoundException {
ResultSet resultSet = userDao.findByName(name);
boolean flag = true;
while(resultSet.next()){
flag = false;
System.out.println("查找信息如下");
user user2 = new user(resultSet.getInt(1),
resultSet.getString(2),
resultSet.getString(3),
resultSet.getString(4),
resultSet.getString(5),
resultSet.getString(6));
System.out.println(user2.toString());
}
if(flag) System.out.println("没有此人!");
}
//检测姓名和密码是否对应
static void testCheck() throws Exception {
if(userDao.checkUser("zk", "1234")) {
testFindByName("zk");
}
else System.out.println("账号或密码错误");
}
//检测删除
static void testDelete() throws ClassNotFoundException, SQLException {
String[] username = {"zk"};
String[] userpassword = {"201711010416"};
for(int i = 0;i < username.length;i++) {
if(userDao.delete(username[i],userpassword[i])){
System.out.println("成功删除" + username[i]);
}
else {
System.out.println( username[i]+"删除失败");
}
}
}
}
七、JDBC底层的实现
八、一些问题
1. 关于mysql
2. 关于JDBC#
九、相关信息
- 代码链接
- 本文为原创,转载请输入标明。