1.属性类,存储属性以及set,get,toString方法
package java_n20.entity;
import java.util.Date;
/**
* ClassName: Emp
* @Description: 属性类
* @author cai
* @date 2018年10月23日
*/
public class Emp {
private Integer id;
private String name;
private String sex;
private Date birth;
private String departemnt;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@Override
public String toString() {
return "Emp [id=" + id + ", name=" + name + ", sex=" + sex + ", birth=" + birth + ", departemnt=" + departemnt
+ ", address=" + address + "]";
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public String getDepartemnt() {
return departemnt;
}
public void setDepartemnt(String departemnt) {
this.departemnt = departemnt;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
2.配置文件
jdbc.name = root
jdbc.password = 2510.62
jdbc.url = jdbc:mysql://localhost/demo?useSSL=true&characterEncoding=utf8
jdbc.driver = com.mysql.jdbc.Driver
3.Mysql连接工具类
package java_n20.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* ClassName: DBHelper
* @Description: 获得数据库连接的工具类
* @author cai
* @date 2018年10月22日
*/
public class DBHelper {
//1.用户名
private static String name;
//2.密码
private static String password;
//3.地址
private static String url;
//4.驱动,从外部引进mysql-connector-jar文件
private static String driver;
static {
Properties prop = new Properties();
try {
//1.读取jdbc.properties文件的内容
prop.load(new FileInputStream(new File("src/jdbc.properties")));
name = prop.getProperty("jdbc.name");
url = prop.getProperty("jdbc.url");
password = prop.getProperty("jdbc.password");
driver = prop.getProperty("jdbc.driver");
//2.加载驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getDBConnection() { //工具类方法一般为静态,而且是不能实例化的
Connection conn= null;
try {
//3.获得连接
conn= DriverManager.getConnection(url, name, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private DBHelper() {
}
}
4.接口类
package java_n20.dao;
import java.util.List;
import java_n20.entity.Emp;
/**
* ClassName: EmpDao
* @Description: 接口,统一方法
* @author cai
* @date 2018年10月23日
*/
public interface EmpDao {
public Integer addEmp(Emp emp) throws Exception; //因为传输的内容比较多,所以使用对象方便一些
public Integer deleteEmp(int id) throws Exception;
public Integer updateEmp(Emp emp) throws Exception; //修改字段的时候,要修改的数量并不一定,所以使用对象
public Emp selectOneEmp(String name) throws Exception;
public List<Emp> selectAll() throws Exception;
public void selectByPage() throws Exception;
public void selectCount() throws Exception;
}
5.接口实现类
package java_n20.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java_n20.dao.EmpDao;
import java_n20.entity.Emp;
import java_n20.util.DBHelper;
/**
* ClassName: EmpDaoImpl
* @Description: 接口实现类
* @author cai
* @date 2018年10月23日
*/
public class EmpDaoImpl implements EmpDao{
private Connection conn = null;
private PreparedStatement ps = null;
ResultSet rs =null;
/**
* 添加员工 insert
*/
@Override
public Integer addEmp(Emp emp) throws Exception {
//1.获得数据库连接
conn = DBHelper.getDBConnection();
//2.准备sql语句
String sql = "insert into student (id,name) values (?,?)";
//3.(预)编译sql Statement-->Preparement(预编译对象,存放的是编译后的sql语句)
//Preparement 用于将参数化的sql语句发送到数据库
//PreparedStatement表示预编译的sql语句的对象,sql语句已预编译并存储在这个对象中
ps = conn.prepareStatement(sql);
//3.1如果有占位符,此处给占位符赋值
ps.setInt(1,emp.getId()); //(占位符位置,参数) 比如此处第一个占位符
ps.setString(2, emp.getName());
//4.执行sql添加
int num = ps.executeUpdate(); //返回值表示更改的记录数,返回0一般表示失败
//5.关闭连接,释放资源
conn.close();
return num;
}
@Override
public Integer deleteEmp(int id) throws Exception {
conn = DBHelper.getDBConnection();
String sql = "delete from student where id = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
//4.执行sql删除
int num = ps.executeUpdate();
conn.close();
return num;
}
@Override
public Integer updateEmp(Emp emp) throws Exception {
conn = DBHelper.getDBConnection();
String sql = "update student set name=? where id = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, emp.getName());
ps.setInt(2, emp.getId());
//4.执行sql更改
int num = ps.executeUpdate();
conn.close();
return num;
}
@Override
public Emp selectOneEmp(String na) throws Exception {
conn = DBHelper.getDBConnection();
String sql = "select * from student where name = ccc";
ps = conn.prepareStatement(sql);
ps.setString(1, na);
//4.执行sql查询
rs = ps.executeQuery(); //返回一个结果集,是数据库里面查询的数据信息
//4.1获取返回的数据信息
Emp emp = new Emp();
if(rs.next()) { //指针下一行有没有数据
String name = rs.getString("name"); //参数为列信息
String sex = rs.getString("sex");
emp.setName(name);
emp.setSex(sex);
}
//5.关闭
rs.close();
ps.close();
conn.close();
return emp;
}
@Override
public List<Emp> selectAll() throws Exception {
conn = DBHelper.getDBConnection();
String sql = "select * from student";
ps = conn.prepareStatement(sql);
//4.执行sql查询
rs = ps.executeQuery(); //返回一个结果集,是数据库里面查询的数据信息
//4.1获取返回的数据信息
List<Emp> empList=new ArrayList<>();
while(rs.next()) { //指针下一行有没有数据
String name = rs.getString("name"); //参数为列信息
String sex = rs.getString("sex");
Emp emp = new Emp();
emp.setName(name);
emp.setName(sex);
empList.add(emp);
}
//5.关闭
rs.close();
ps.close();
conn.close();
return null;
}
@Override
public void selectByPage() throws Exception {
conn = DBHelper.getDBConnection();
String sql = "select * from student limit 0,5";
ps = conn.prepareStatement(sql);
//4.执行sql查询
rs = ps.executeQuery(); //返回一个结果集,是数据库里面查询的数据信息
//4.1获取返回的数据信息
while(rs.next()) { //指针下一行有没有数据
String name = rs.getString("name"); //参数为列信息
String sex = rs.getString("sex");
System.out.println(name+sex);
}
//5.关闭
rs.close();
ps.close();
conn.close();
}
@Override
public void selectCount() throws Exception {
conn = DBHelper.getDBConnection();
String sql = "select count(*) from student";
ps = conn.prepareStatement(sql);
//4.执行sql查询
rs = ps.executeQuery(); //返回一个结果集,是数据库里面查询的数据信息
//4.1获取返回的数据信息
while(rs.next()) { //指针下一行有没有数据
int total = rs.getInt("count(*)");
System.out.println(total);
}
//5.关闭
rs.close();
ps.close();
conn.close();
}
}
6.测试类
package java_n20.test;
import java_n20.dao.EmpDao;
import java_n20.dao.impl.EmpDaoImpl;
import java_n20.entity.Emp;
/**
* ClassName: TestEmp
* @Description: 测试类
* @author cai
* @date 2018年10月23日
*/
public class TestEmp {
public static void main(String[] args) {
EmpDao empDao = new EmpDaoImpl();
//要传输的数据
int id = 000;
String name = "cc";
Emp emp = new Emp();
emp.setId(id);
emp.setName(name);
try {
int num= empDao.addEmp(emp);
if(num==1){
System.out.println("添加成功");
}
empDao.updateEmp(emp);
} catch (Exception e) {
e.printStackTrace();
}
}
}