员工jdbc抽象
员工bean类
package
mySQL;
import
java.sql.Date;
/**
* 公民实体类
*
@author
asus
*
*/
public
class
Manbean {
/**编号*/
private
int
id
;
/**姓名*/
private
String
name
;
/**性别*/
private
String
sex
;
/**生日*/
private
Date
birthday
;
public
int
getId() {
return
id
;
}
public
void
setId(
int
id
) {
this
.
id
=
id
;
}
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 getBirthday() {
return
birthday
;
}
public
void
setBirthday(Date
birthday
) {
this
.
birthday
=
birthday
;
}
@Override
public
String toString() {
return
"Manbean [id="
+
id
+
", name="
+
name
+
", sex="
+
sex
+
", birthday="
+
birthday
+
"]"
;
}
public
Manbean(String
name
, String
sex
, Date
birthday
) {
super
();
this
.
name
=
name
;
this
.
sex
=
sex
;
this
.
birthday
=
birthday
;
}
public
Manbean() {
super
();
//
TODO
Auto-generated constructor stub
}
}
员工方法接口类
package mySQL;
import java.sql.Date;
import java.util.List;
/**
* 公民持久化接口
* @author asus
*
*/
public interface IManDao {
/**
* 添加
* @param man
*/
public void add(Manbean man);
/**
* 删除
* @param id
*/
public void del(int id);
/**
*按照id修改生日
* @param id
* @param birthday
*/
public void update(int id,Date birthday);
/**
* 查询所有公民
* @return
*/
public List<Manbean> findAll();
/**
* 按id查询公民
* @param id
* @return
*/
public Manbean findById(int id);
/**
* 按照选项查询
* @param name 姓名
* @param starDate 生日其实日期
* @param endDate 生日结束日期
* @return 公民集合
*/
public List<Manbean> findByItem(String name,Date starDate,Date endDate);
}
员工实现方法类(其中的删除和查询我用的是抽象方法,其他的都是普通没有抽象的发给大家看看)
package mySQL;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 公民持久接口实现类
*
* @author asus
*
*/
public class ManDaoImpl extends Base implements IManDao {
@Override
public void add(Manbean man) {
Connection con = null;
PreparedStatement ps = null;
// 加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
// 建立连接
// jdbc表示需要使用jdbc建立连接,mysql表示连接数据库类型,localhost表示连接服务器的ip地址,体重localhost为本机的ip
// 3306表示连接mysql的端口号,test为mysql中的数据库名称,表示使用该库中的数据库表
// characterEncoding=utf-8表示连接数据库的编码题
con = DriverManager.getConnection("jdbc:
mysql://localhost:3306/test?characterEncoding=utf-8
", "root",
"1234");
System.out.println(con);
// ?号表示站位符,需要向占位符填充数据
ps = con.prepareStatement("insert into t_man(manName,Sex,birthday) values(?,?,?)");
// 将man对象中的name属性取出,填充第一个占位符
ps.setString(1, man.getName());
ps.setString(2, man.getSex());
ps.setDate(3, man.getBirthday());
// 更新数据库
ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
ps.close();
con.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public void del(int id) {
/*
* PreparedStatement ps=null; Connection con=null; try {
* Class.forName("com.mysql.jdbc.Driver");
* con=DriverManager.getConnection
* "root","1234");
*
* ps=con.prepareStatement("delete from t_man where id=?"); ps.setInt(1,
* id); ps.executeUpdate(); } catch (Exception e) { // TODO
* Auto-generated catch block e.printStackTrace(); }finally{ try {
* ps.close(); con.close(); } catch (Exception e) { // TODO
* Auto-generated catch block e.printStackTrace(); }
*
* }
*/
this.setConnection();
try {
ps = con.prepareStatement("delete from t_man where id=?");
ps.setInt(1, id);
ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
this.closeConnection();
}
}
@Override
public void update(int id, Date birthday) {
Connection con = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:
mysql://localhost:3306/test?characterEncoding=utf-8
", "root",
"1234");
ps = con.prepareStatement("update t_man set birthday=? where id=?");
ps.setDate(1, birthday);
ps.setInt(2, id);
ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
ps.close();
con.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public List<Manbean> findAll() {
List<Manbean> list = new ArrayList<Manbean>();
/*Connection con = null;
PreparedStatement ps = null;
// 结果集
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:
mysql://localhost:3306/test?characterEncoding=utf-8
", "root",
"1234");
ps = con.prepareStatement("select * from t_man");
// 执行查询操作,将sql语句查询的数据,封装再结果集对象中
rs = ps.executeQuery();
// 将结果集的指针,不断指向吓一跳记录,如果该方法返回为false,表示指针到达结果集末尾
while (rs.next()) {
Manbean man = new Manbean();
// 将数据库表中id列的值取出,填充实体对象的id属性
man.setId(rs.getInt("id"));
man.setName(rs.getString("manName"));
man.setSex(rs.getString("sex"));
man.setBirthday(rs.getDate("birthday"));
// 将封装好记录的实体对象加入集合
list.add(man);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
ps.close();
con.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}*/
this.setConnection();
try {
ps=con.prepareStatement("select * from t_man");
rs=ps.executeQuery();
while(rs.next()){
Manbean man = new Manbean();
man.setId(rs.getInt("id"));
man.setName(rs.getString("manName"));
man.setSex(rs.getString("sex"));
man.setBirthday(rs.getDate("birthday"));
list.add(man);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
this.closeConnection();
}
return list;
}
@Override
public Manbean findById(int id) {
Manbean man = new Manbean();
Connection con = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:
mysql://localhost:3306/test?characterEncoding=utf-8
", "root",
"1234");
ps = con.prepareStatement("select * from t_man where id=?");
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
man.setId(rs.getInt("id"));
man.setName(rs.getString("manName"));
man.setSex(rs.getString("sex"));
man.setBirthday(rs.getDate("birthday"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
con.close();
ps.close();
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return man;
}
@Override
public List<Manbean> findByItem(String name, Date starDate, Date endDate) {
List<Manbean> list = new ArrayList<Manbean>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:
mysql://localhost:3306/test?characterEncoding=utf-8
", "root",
"1234");
ps = con.prepareStatement("select * from t_man where manName like ? and birthday>=? and birthday<=?");
// 模糊查询
ps.setString(1, "%" + name + "%");
ps.setDate(2, starDate);
ps.setDate(3, endDate);
rs = ps.executeQuery();
while (rs.next()) {
Manbean m = new Manbean();
m.setId(rs.getInt("id"));
m.setName(rs.getString("manName"));
m.setSex(rs.getString("sex"));
m.setBirthday(rs.getDate("birthday"));
list.add(m);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
con.close();
ps.close();
rs.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return list;
}
public static void main(String[] args) {
IManDao dao = new ManDaoImpl();
// dao.add(new Manbean("王五","男",Date.valueOf("1992-02-03")));
// dao.del(1);
// dao.update(1, Date.valueOf("1999-04-28"));
List<Manbean> list=dao.findAll();
System.out.print(list);
// Manbean manbean=dao.findById(1);
// System.out.println(manbean);
// List<Manbean> list = dao.findByItem("张", Date.valueOf("1990-01-01"), Date.valueOf("2000-01-01"));
// System.out.println(list);
}
}
这个是抽象
适用于所有
package mySQL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Base {
protected Connection con;
protected PreparedStatement ps;
protected ResultSet rs;
public void setConnection(){
try {
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8"
,"root","1234");
System.out.println(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void closeConnection(){
try {
if(rs!=null){
rs.close();
}
ps.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
}
}