JDBC_DAO模式
包结构
- src
- dao
- UserDao.java
- dao.impl
- UserDaoimpl.java
- util
- JDBCUtil.java
- test
- Test.java
- jdbc.properties.txt
- dao
- lib
- mysql-connector-java-bin.jar
完整代码
jdbc.properties
#mysql
driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/emp
user = root
password = 1230
#oracl
#driver = oracle.jdbc.driver.OracleDriver
#jdbcUrl = jdbc:oracle:thin:@localhost:1521:orcl
#user = root
#password = 1230JDBCUtil.java
package util;
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 JDBCUtil {
static String driverClass= null;
static String url = null;
static String user = null;
static String password = null;
static {
try {
//创建一个属性配置对象
Properties properties = new Properties();
//根工程目录
// InputStream in = new FileInputStream("jdbc.properties");
//src路径
//使用类加载器，读取配置文件
InputStream in = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
//输入流
properties.load(in);
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConn() {
Connection conn = null;
try {
//4.0以上版本不用注册驱动
//Class.forName(driverClass);
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
/**
* 释放资源
* @param conn
* @param st
*/
public static void release(Connection conn, Statement st, ResultSet rs) {
closeRs(rs);
closeSt(st);
closeConn(conn);
}
public static void release(Connection conn, Statement st) {
closeSt(st);
closeConn(conn);
}
public static void closeRs(ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
rs=null;
}
}
public static void closeSt(Statement st) {
try {
if(st != null) {
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
st=null;
}
}
public static void closeConn(Connection conn) {
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
conn=null;
}
}
}UserDao.java
package dao;
public interface UserDao {
/**
* 根据id去更新具体的用户名
* @param id
* @param name
*/
void update(int id , String name);
void delete(int id);
/**
* 执行添加
* @param userName
* @param password
*/
void insert(String userName , String password);
/**
* 查询所有
*/
void findAll();
/**
* 登录方法
* @param username
* @param password
*/
void login(String username , String password);
}UserDaoImpl.java
package dao.impl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import dao.UserDao;
import util.JDBCUtil;
public class UserDaoImpl implements UserDao{
/**
* 查询所有
*/
@Override
public void findAll() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//1. 创建连接对象
conn = JDBCUtil.getConn();
//2. 创建statement对象
st = conn.createStatement();
String sql="select * from tt";
rs = st.executeQuery(sql);
while(rs.next()){
String name = rs.getString("name");
String age = rs.getString("age");
System.out.println(name+":"+age);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st, rs);
}
}
/**
* 登陆校验
*/
/*@Override
public void login(String username, String password) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//1. 获取连接对象
conn = JDBCUtil.getConn();
//2. 创建statement对象
st = conn.createStatement();
// SELECT * FROM t_user WHERE username='admin' AND PASSWORD='10086'
String sql = "select * from t_user where username='"+ username +"' and password='"+ or +"'";
rs = st.executeQuery(sql);
if(rs.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st, rs);
}
}*/
/**
* 登陆校验
*/
@Override
public void login(String username,String password) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
PreparedStatement ps = null;
String sql = "";
try {
conn = JDBCUtil.getConn();
// st = conn.createStatement();
//2.预先对sql语句执行语法校验， ？ 对应的内容，后面不管什么传递进来，都当做字符串
sql = "select * from login where username=? and password=?";
ps = conn.prepareStatement(sql);
// ? 索引从1开始
ps.setString(1, username);
ps.setString(2, password);
rs = ps.executeQuery();
if(rs.next()) {
System.out.println("登陆成功");
}else {
System.out.println("登陆失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st, rs);
}
}
/**
* 插入
*/
@Override
public void insert(String username, String password) {
Connection conn = null;
Statement st = null;
PreparedStatement ps = null;
String sql = "";
try {
conn = JDBCUtil.getConn();
sql = "insert into tt values(4 , ? , ?)";
// st = conn.createStatement();
//2.预先对sql语句执行语法校验， ？ 对应的内容，后面不管什么传递进来，都当做字符串
ps = conn.prepareStatement(sql);
//?索引从1开始
ps.setString(1, username);
ps.setString(2, password);
int result = ps.executeUpdate();
if(result>0) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
/**
* 删除
*/
@Override
public void delete(int id) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "";
try {
conn = JDBCUtil.getConn();
sql = "delete from tt where id=?";
// st = conn.createStatement();
//2.预先对sql语句执行语法校验， ？ 对应的内容，后面不管什么传递进来，都当做字符串
ps = conn.prepareStatement(sql);
//?索引从 1 开始
ps.setInt(1, id);
int result = ps.executeUpdate();
if(result>0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
/**
* 更新
*/
@Override
public void update(int id, String name) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "";
try {
conn = JDBCUtil.getConn();
sql = "update tt set id=? where name=?";
// st = conn.createStatement();
//2.预先对sql语句执行语法校验， ？ 对应的内容，后面不管什么传递进来，都当做字符串
ps = conn.prepareStatement(sql);
//?索引从1开始
ps.setInt(1, id);
ps.setString(2, name);
int result = ps.executeUpdate();
if(result>0) {
System.out.println("更新成功");
}else {
System.out.println("更新失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
}Test.java
package test;
import org.junit.Test;
import dao.UserDao;
import dao.impl.UserDaoImpl;
public class TestUserDaoImpl {
@Test
public void testFindAll(){
UserDao dao = new UserDaoImpl();
dao.findAll();
}
@Test
public void testlogin(){
UserDao dao = new UserDaoImpl();
dao.login("lishishi", "45612213321");
}
@Test
public void testInsert(){
UserDao dao = new UserDaoImpl();
dao.insert("wangwuwu","789");
}
@Test
public void testDelete(){
UserDao dao = new UserDaoImpl();
dao.delete(4);
}
@Test
public void testUpdate(){
UserDao dao = new UserDaoImpl();
dao.update(99, "gengxin");
}
}原文作者: 冯亚杰(DanBoard·Feng)
原文链接: http://danbo3110.github.io/2019/10/23/JDBC_DAO模式/
版权声明: 转载请注明出处(必须保留作者署名及链接)