具体什么是DAO模式,请看 java中Dao模式
1、java的DAO模式:
Data Access Object(数据存取对象)
这个也是设计模式之一,在数据库中用的比较多,也是java多态的一种体现,接口+实现类+工具类,将数据和代码分开,网上的解释都很专业,反正你只需要知道这个代码结构更加花哨更加鲁棒就行了
DAO模式的组成部分
- DAO接口
- DAO实现类
- 实体类(测试类)
- 数据库连接和关闭工具类
下面直接上代码,其实就是前面连接数据库并查询的代码的重构:
先看一下工程结构(将一段代码放到四个包里,是不是很花哨)
代码:
- DaoInterface:DAO接口
package cn.nupt.dao;
public interface DaoInterface {
void Query();
}
- DaoImplement:DAO实现类
package cn.nupt.imple;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import cn.nupt.dao.DaoInterface;
import cn.nupt.util.CloseUtil;
public class DaoImplement implements DaoInterface {
// public String sql;
private Connection con;
private Statement st;
private ResultSet rs;
@Override
public void Query() {
try {
con = CloseUtil.registerDriverAndGetConnection();
st = con.createStatement();
String sql = "select * from websites";
rs = st.executeQuery(sql);
while (rs.next()) {
String name = rs.getString("name");
String url = rs.getString("url");
System.out.println(name + "=" + url);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
CloseUtil.release(rs, st, con);
}
}
}
- TestDaoQuery:实体类/测试类
package cn.nupt.test;
import org.junit.jupiter.api.Test;
import cn.nupt.dao.DaoInterface;
import cn.nupt.imple.DaoImplement;
public class DaoTest {
@Test
public void TestDaoQuery() {
DaoInterface dao = new DaoImplement();
dao.Query();
}
}
- CloseUtil:数据库连接和关闭工具类
package cn.nupt.util;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
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 CloseUtil {
static String sqlName = null;
static String url = null;
static String name = null;
static String password = null;
static {
try {
Properties properties = new Properties();
// void load(InputStream inStream)
// .properties放在工程目录下
InputStream in = new FileInputStream("jdbc.properties");
// .properties放在src目录下
// InputStream in =
// CloseUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(in);
sqlName = properties.getProperty("sqlName");
url = properties.getProperty("url");
name = properties.getProperty("name");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection registerDriverAndGetConnection() {
Connection con = null;
try {
Class.forName(sqlName).newInstance();
con = DriverManager.getConnection(url, name, password);
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public static void release(ResultSet rs, Statement st, Connection con) {
CloseRS(rs);
CloseST(st);
CloseCon(con);
}
//重载,用于除了查询的释放资源
public static void release(Statement st, Connection con) {
CloseST(st);
CloseCon(con);
}
private static void CloseRS(ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
private static void CloseST(Statement st) {
try {
if (st != null) {
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
st = null;
}
}
private static void CloseCon(Connection con) {
try {
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
con = null;
}
}
}
jdbc.properties:配置文件
sqlName = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost/pin?useSSL=false
name = root
password = 1111
MySql表:pin/websites
输出:
2、用PreparedStatement优化Statement:
PreparedStatement是Statement的子类,所以最好关流的时候只要关statement就行了
statement执行,其实是拼接语句的,先拼接sql语句,然后再一起执行,如果变量前面带有数据库关键字,那么会一并认为是关键字,这就会出现错误了
用preparedstatement预处理sql语句,解决这个问题
- 将sql语句中的变量用占位符?代替
- 用setString()方法传入参数,从1开始
具体代码如下
public void login(String name, int alexa) {
try {
con = CloseUtil.registerDriverAndGetConnection();
//1. 将sql语句中的变量用占位符?代替
String sql = "select * from websites where name=? and alexa=?";
pst = con.prepareStatement(sql);
//2. 用setString()方法传入参数,从1开始
pst.setString(1, name);
pst.setLong(2, alexa);
ResultSet rs = pst.executeQuery();
if (rs.next()) {
System.out.println("登陆成功");
}else {
System.out.println("登陆失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
CloseUtil.release(rs, st, con);
}
}