driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/student
name=root
password=123456
创建接口(父类引用指向子类对向)提高代码的复用性(灵活性)
package com.ny.dao;
public interface UserDao {
/*
* 更新数据
*/
void update(int id,String password);
/*
* 删除数据
*/
void delete(int id);
/*
* 插入数据
* 要带参数
*/
void insert(String username,String password);
/*
* 查询所有的数据
*/
void findAll();
}
实现接口重写接口里面的所有方法
PrepareStatement
该对象就是替换前面(JDBC_DAO的增删改查一)的statement对象。
1. 相比较以前的statement, 预先处理给定的sql语句,对其执行语法检查。 在sql语句里面使用 ? 占位符来替代后续要传递进来的变量。 后面进来的变量值,将会被看成是字符串,不会产生任何的关键字。
package com.ny.dao.implement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.ny.dao.UserDao;
import com.ny.util.MysqlDemoUtil;
public class UserDaoImp implements UserDao{
@Override
public void findAll() {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//建立连接
con = MysqlDemoUtil.getCon();
//创建parperstatement
String sql = "select * from user";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
String name = rs.getString("username");
String password = rs.getString("password");
System.out.println(name + " " + password);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
MysqlDemoUtil.closeAll(rs, ps, con);
}
}
//插入操作
@Override
public void insert(String username, String password) {
Connection con = null;
PreparedStatement ps = null;
try {
//建立连接
con = MysqlDemoUtil.getCon();
//
String sql = "insert into user values(null,?,?)";
ps = con.prepareStatement(sql);
ps.setString(1, username);
ps.setString(2, password);
int i = ps.executeUpdate();
if(i > 0) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
MysqlDemoUtil.closeAll(ps, con);
}
}
//删除操作
public void delete(int id) {
Connection con = null;
PreparedStatement ps = null;
try {
//建立连接
con = MysqlDemoUtil.getCon();
//创建parpertatementt
String sql = "delete from user where id = ?";
ps = con.prepareStatement(sql);
ps.setInt(1, id);
int i = ps.executeUpdate();
if(i > 0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
MysqlDemoUtil.closeAll(ps, con);
}
}
//更新操作
public void update(int id, String password ) {
Connection con = null;
PreparedStatement ps = null;
try {
//创建连接
con = MysqlDemoUtil.getCon();
//创建parpersattement
String sql = "update user set password = ? where id = ?";
ps = con.prepareStatement(sql);
ps.setString(1, password);
ps.setInt(2, id);
int i = ps.executeUpdate();
if(i > 0) {
System.out.println("更新成功");
}else {
System.out.println("更新失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
MysqlDemoUtil.closeAll(ps, con);
}
}
}
创建测试单元
添加junit的支持 右键工程 --- add Library --- Junit --- Junit4
import org.junit.Test;
import com.ny.dao.UserDao;
import com.ny.dao.implement.UserDaoImp;
public class TestUserDaoImp {
// @Test
public void testFindAll() {
UserDao ud = new UserDaoImp();
ud.findAll();
}
// @Test
public void testInsert() {
UserDao ud = new UserDaoImp();
ud.insert("刘德华", "123456");
}
// @Test
public void testDelete() {
UserDao ud = new UserDaoImp();
ud.delete(4);
}
@Test
public void testUpdate() {
UserDao ud = new UserDaoImp();
ud.update(1, "12345678900");
}
}
创建JDBC的工具类
package com.ny.util;
import java.io.FileInputStream;
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 MysqlDemoUtil {
//static关键字修饰的变量供所有实例共享
static String driverClass = null;
static String url = null;
static String name = null;
static String password = null;
static {
try{
//创建属性配置对象
Properties pro = new Properties();
//使用类加载器,去读取src底下的资源文件。 后面在servlet
// InputStream is = MysqlDemoUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
InputStream is = new FileInputStream("jdbc.properties");
//导入输入流
pro.load(is);
//读取属性
driverClass = pro.getProperty("driverClass");
url = pro.getProperty("url");
name = pro.getProperty("name");
password = pro.getProperty("password");
}catch (Exception e) {
e.printStackTrace();
}
}
//获取连接对象
public static Connection getCon() {
Connection con = null;
try {
//加载驱动程序
Class.forName(driverClass );
//建立连接
con = DriverManager.getConnection(url,name,password);
} catch (Exception e) {
e.printStackTrace();
}
return con ;
}
//这样做提高了代码的复用性 是程序变得更加灵活
public static void closeAll(ResultSet rs,Statement st,Connection con) {
rsClose(rs);
stClose(st);
conClose(con);
}
public static void closeAll(Statement st,Connection con) {
stClose(st);
conClose(con);
}
public static void rsClose(ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void stClose(Statement st) {
try {
if(st != null) {
st.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void conClose(Connection con) {
try {
if(con != null) {
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}