通用增删改查

package com.softeem.jdbc1;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class EmpCRUD extends BaseConn{


public int getTotalNum() throws SQLException{
PreparedStatement ps = getConn().prepareStatement("select count(*) totalnum from employee");
ResultSet rs = ps.executeQuery();
if(rs.next()){
return rs.getInt("totalnum");
}
return 0;
}

public void queryByPage(int currentPage,int pageSize) throws SQLException{
PreparedStatement ps = getConn().prepareStatement("select * from employee limit ?,?");
ps.setInt(1, (currentPage-1)*pageSize);
ps.setInt(2, pageSize);
ResultSet rs = ps.executeQuery();
while(rs.next())
{
int num = rs.getInt("num");
String name = rs.getString("name");
String addr = rs.getString("addr");
Date date = rs.getDate("birth");

System.out.println(num+"--"+name+"--"+addr+"--"+date);
}
rs.close();
ps.close();
}

public List<Object[]> query() throws SQLException{
PreparedStatement ps = getConn().prepareStatement("select * from department limit 0,10");
ResultSet rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取查询列总数
int count = rsmd.getColumnCount();
List<Object[]> list = new ArrayList<>();
while(rs.next()){
//每读取到一行记录声明一个数组
Object[] objs = new Object[count];
for (int i = 1; i <= count; i++) {
//获取列标签名(别名，若不存在则用列名)
String label = rsmd.getColumnLabel(i);
//获取列名称
String cname = rsmd.getColumnName(i);
//获取列数据类型
// int type = rsmd.getColumnType(i);
// if(type == java.sql.Types.INTEGER){
// int num = rs.getInt(label);
// System.out.print(num+"  ");
// }else if(type == java.sql.Types.VARCHAR){
// String value = rs.getString(label);
// System.out.print(value+"  ");
// }
//获取一列数据
Object obj = rs.getObject(label);
//将列数据装入数组中
objs[i-1] = obj;
}
//将装有多列数据的一行结果装入集合
list.add(objs);
}
return list;
}

public void add(Emp emp) throws SQLException{
String sql = "insert into employee(name,sex,tel,addr,email,zip,depno,birth) values(?,?,?,?,?,?,?,?)";
PreparedStatement ps = getConn().prepareStatement(sql);
ps.setString(1,emp.getName());
ps.setString(2,emp.getSex());
ps.setString(3,emp.getTel());
ps.setString(4,emp.getAddr());
ps.setString(5,emp.getEmail());
ps.setString(6,emp.getZip());
ps.setInt(7, emp.getDepno());
ps.setDate(8, new java.sql.Date(emp.getBirth().getTime()));
int i = ps.executeUpdate();
System.out.println("执行结果:"+i);
}

public List<Emp> findAll() throws SQLException{
List<Emp> list = new ArrayList<>();
String sql = "select num,name,sex,tel,addr,email,zip,depno,birth from employee limit 0,10";
PreparedStatement ps = getConn().prepareStatement(sql);
ResultSet rs = ps.executeQuery();
Emp emp = null;
while(rs.next()){
int num = rs.getInt("num");
String name = rs.getString("name");
String addr = rs.getString("addr");
String email = rs.getString("email");
String sex = rs.getString("sex");
String tel = rs.getString("tel");
String zip = rs.getString("zip");
int depno = rs.getInt("depno");
Date birth = rs.getDate("birth");

emp = new Emp(name, addr, zip, email, tel, sex, birth, depno);
emp.setNum(num);
list.add(emp);
}
return list;
}

/**
* 当前页
* 每页大小
* 如何使用jdbc链接数据库完成一个分页操作
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
// new EmpCRUD().queryByPage(3, 10);
// List<Object[]> list = new EmpCRUD().query();
// for (Object[] objects : list) {
// for (int i = 0; i < objects.length; i++) {
// System.out.print(objects[i]+"  ");
// }
// System.out.println();
// }
Emp emp = new Emp("狗蛋", "武汉大学", "430000", "hello@sina.com", "123321", "男", new Date(), 2);
// new EmpCRUD().add(emp);
List<Emp> emps = new EmpCRUD().findAll();
for (Emp emp2 : emps) {
System.out.println(emp2);
}
}
}