Day2 JDBC Bean Dao 学习使用 项目搭建
前提:jdk 版本1.8, mysql 版本 5.7, idea版本 2018.2
1. JDBC编写
- 新建普通项目
- 在项目中新建lib文件夹,并导入支持JDBC的jar包,并设置为Library
- 新建数据库和数据表
- 在idea中新建Test类,进行代码编写,实现JDBC。
- 建立数据库连接
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user?useSSL=true&characterEncoding=utf-8&user=root");
System.out.println("connection success");
} catch (Exception e) {
e.printStackTrace();
}
- 编辑sql语句实现增删查改,在util包中封装连接数据库的操作和断开连接的操作
- 查询所有数据
public List<UserInfo> findAll () {
List<UserInfo> list = new ArrayList<>();
try {
connection = DBUtil.getConnection();
String sql1 = "select * from userinfo";
// statement object
statement = connection.prepareStatement(sql1);
// execute sql statement
rs = statement.executeQuery();
// tackle result
while (rs.next()) {
UserInfo userInfo = new UserInfo();
userInfo.setId(rs.getInt(1));
userInfo.setUsername(rs.getString(2));
userInfo.setPassword(rs.getString(3));
list.add(userInfo);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, connection, statement);
}
return list;
8.增删改
connection = DBUtil.getConnection();
try {
statement = connection.prepareStatement(sql);
statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, connection, statement);
}
2.Bean类
Bean类就是实体类,这里需要userInfo这个实体类。在实体类中要实现私有变量的getter和setter方法,并且要覆盖toString检查打印结果。
package com.jeleren.bean;
public class UserInfo {
private int id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int 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;
}
@Override
public String toString() {
return "UserInfo{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
3.Dao类
持久层,与数据库进行交互,增删查改
package com.jeleren.Dao;
import com.jeleren.bean.UserInfo;
import com.jeleren.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserInfoDao {
private Connection connection = null;
private PreparedStatement statement = null;
private ResultSet rs = null;
public List<UserInfo> findAll () {
List<UserInfo> list = new ArrayList<>();
try {
connection = DBUtil.getConnection();
String sql1 = "select * from userinfo";
// statement object
statement = connection.prepareStatement(sql1);
// execute sql statement
rs = statement.executeQuery();
// tackle result
while (rs.next()) {
UserInfo userInfo = new UserInfo();
userInfo.setId(rs.getInt(1));
userInfo.setUsername(rs.getString(2));
userInfo.setPassword(rs.getString(3));
list.add(userInfo);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, connection, statement);
}
return list;
}
public void adu (String sql) {
connection = DBUtil.getConnection();
try {
statement = connection.prepareStatement(sql);
statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, connection, statement);
}
}
}