一.jdbc的七个步骤
1.创建连接数据库的四个参数(driver,数据库url,用户名,密码)
2.加载驱动,前提要导入jar包,用Class.forName(DBdriver),例如
Class.forName("com.mysql.jdbc.Driver");
3.进行数据库连接
Conn = DriverManager.getConnection(URL, UserName, Password);
这里的参数是第一步定义的字符串
4.创建预处理对象
PreparedStatement pstm = null;
pstm = conn.prepareStatement(sqlString);
5.执行SQL语句
rs = statement.executeQuery();
6.遍历结果集
例如
while(rs.next()){
String name = rs.getString(“name”) ;
String pass = rs.getString(1) ; // 此方法比较高效
}
7.处理异常,关闭JDBC对象资源
一般
if (rs != null) {
rs.close();
}
if (pstm != null) {
pstm.close();
}
if (conn != null) {
conn.close();
}
二.基本程序项目分层
有了jdbc的七步骤,我们可以很容易的写出一个简单的数据库查询的程序,例如:
public class Test {
public static void main(String[] args){
Connection conn = null;
PreparedStatement statement = null;
ResultSet rs = null;
try{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?useSSL=true&" +
"characterEncoding=utf-8&user=root&password=123");
System.out.println("创建连接成功");
String query_SQL = "select * from userinfo";
statement = conn.prepareStatement(query_SQL);
rs = statement.executeQuery();
while (rs.next()){
System.out.println(rs.getInt("uid")+"\t\t"+rs.getString("username")+"\t\t"+rs.getString("password"));
}
}
catch (Exception e){
e.printStackTrace();
}
finally {
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
但是我们不可能每执行一次数据库操作都要写这么多繁琐的步骤,所以可以把数据库的相关操作抽象成一个工具类DBUtil,存放到utils文件夹下,在该工具类中实现创建数据库连接对象以及释放资源的方法。
public class DBUtil {
private static Connection Conn; // 数据库连接对象
// 数据库连接地址
private static String URL = "jdbc:mysql://localhost:3306/user?characterEncoding=utf8";
// 数据库的用户名
private static String UserName = "root";
// 数据库的密码
private static String Password = "123";
public static Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("加载驱动成功!!!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
//通过DriverManager类的getConenction方法指定三个参数,连接数据库
Conn = DriverManager.getConnection(URL, UserName, Password);
System.out.println("连接数据库成功!!!");
return Conn;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public void closeConn(ResultSet rs, PreparedStatement pstm, Connection conn) throws Exception {
if (rs != null) {
rs.close();
}
if (pstm != null) {
pstm.close();
}
if (conn != null) {
conn.close();
}
}
}
接下来我们按照mvc的结构将这个简单的应用分层,首项要建立model文件夹,里面存放实体模型,这里用user举例
public class User {
private int id;
private String username;
private String password;
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 User() {
}
public User(String username, String password) {
this.username = username;
this.password = password;
}
}
有了实体,就要想对实体的操作了,这里拿用户登录举例,为了方便,这里先编写view视图层,毕竟有需求才有业务逻辑。简单写一个登录
public class LoginUI {
public static void main(String[] args){
Scanner inp = new Scanner(System.in);
while(true){
System.out.println("请输入用户名:");
String username = inp.next();
System.out.println("请输入密码:");
String password = inp.next();
boolean result = new LoginServiceImpl().doLogin(new User(username,password));
System.out.println(result);
}
}
}
代码中有LoginServiceImpl(),这里相当于在controller控制层,这一层存放在文件夹service,LoginServiceImpl实现的是LoginService接口,在LoginServiceImpl中去调用model层去执行对数据库处理的操作
public class LoginServiceImpl implements LoginService {
@Override
public boolean doLogin(User loginUser) {
return new CUser().queryUser(loginUser);
}
}
这层中使用了CUser().queryUser(loginUser);
这些对象与方法存放在dao文件夹,dao就像是一个orm(对象关系映射)从dao层看,对数据库的操作就像是对类和对象的操作一样。
同样,dao也是接口
public interface UserDao {
void insertUser(User newUser) throws Exception; // 添加用户的方法
void deleteUser(int id) throws Exception; // 删除用户的方法
void updateUser(int id,User modUser) throws Exception; // 更新用户的方法
void selectByID(int id) throws Exception; // 根据id查询用户
boolean queryUser(User queryUser) throws Exception; // 根据用户名密码查询用户
}
我们在CDao中实现这些接口,下面是CDao文件夹下的UserDao类
public class CUser implements UserDao{
DBHelp dh = new DBHelp();
private Connection conn;
@Override
public void insertUser(User newUser) throws Exception {
// 将用户信息添加到后台数据库表中
Connection conn = dh.getConnection();
PreparedStatement pstm = null;
String sql_insert = "insert into userinfo(username,password) values(?,?)";
pstm = conn.prepareStatement(sql_insert);
pstm.setString(1, newUser.getUsername());
pstm.setString(2, newUser.getPassword());
int row = pstm.executeUpdate();
System.out.println("新增用户成功" + row + "行受到影响");
dh.closeConn(null, pstm, conn);
}
@Override
public void deleteUser(int id) throws Exception{
Connection conn = dh.getConnection();
PreparedStatement pstm = null;
String sql_delete = "delete from userinfo where uid=?";
pstm = conn.prepareStatement(sql_delete);
pstm.setInt(1, id);
int row = pstm.executeUpdate();
System.out.println("删除用户成功" + row + "行受到影响");
dh.closeConn(null, pstm, conn);
}
@Override
public void updateUser(int id,User modUser) throws Exception{
Connection conn = dh.getConnection();
PreparedStatement pstm = null;
String sql_update = "update userinfo set username=? password=? where uid=?";
pstm = conn.prepareStatement(sql_update);
pstm.setInt(1, id);
pstm.setString(2, modUser.getUsername());
pstm.setString(3, modUser.getPassword());
int row = pstm.executeUpdate();
System.out.println("更改用户成功" + row + "行受到影响");
dh.closeConn(null, pstm, conn);
}
@Override
public void selectByID(int id) throws Exception{
Connection conn = dh.getConnection();
PreparedStatement pstm = null;
ResultSet rs = null;
String sql_selectById = "select * from userinfo where uid=?";
pstm = conn.prepareStatement(sql_selectById);
pstm.setInt(1,id);
rs = pstm.executeQuery();
System.out.println("查询成功,信息如下:");
System.out.println("用户编号\t\t用户名称\t\t用户密码");
while (rs.next()) {
System.out.println(rs.getInt("uid") + "\t\t\t" + rs.getString("username") + "\t\t" + rs.getString("password"));
}
}
@Override
public boolean queryUser(User queryUser) {
Connection conn = dh.getConnection();
PreparedStatement pstm = null;
ResultSet rs = null;
String query_SQL = "select count(*) as num from userinfo where username=? and password=?";
try {
pstm = conn.prepareStatement(query_SQL);
pstm.setString(1,queryUser.getUsername());
pstm.setString(2,queryUser.getPassword());
rs = pstm.executeQuery();
while(rs.next()){
int num = rs.getInt("num");
if (num>0){
return true;
}
else {
return false;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
}
UserDao也就是LoginService中使用的函数了。
最后看一下完整的目录结构