本人的java笔记,仅为方便自己参考。
实体:Person
private int id;
private String name;
private String mobile;
private String telphone;
private String email;
private String city;
private Date birthday;
工具类:封装的JDBCUtil
public class JDBCUtil {
private static Properties pro = new Properties();
//创建ThreadLocal
private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
static{
InputStream is = JDBCUtil.class.getResourceAsStream("/com/lau/conf/jdbc.properties");
try {
System.out.println("这是:"+is);
pro.load(is);
Class.forName(pro.getProperty("jdbc.driver"));
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(is!=null){
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static Connection getConn(){
Connection conn = tl.get();//从ThreadLocal中获得Connection
if(conn==null){//第一次调用
try {
conn = DriverManager.getConnection(pro.getProperty("jdbc.url"), pro.getProperty("jdbc.username"), pro.getProperty("jdbc.password"));
tl.set(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return conn;
}
public static void getClose(Connection conn,Statement stmt,ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
tl.remove();//*******************一定要移除
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
dao:接口
public interface PersonDAO {
// 插入数据
void insertPerson(Person p);
// 修改数据
void updatePerson(Person p);
// 删除
void deletePerson(int id);
// 查询全部
List<Person> selectAll();
// 根据名字查询
List<Person> selectByName(String s);
// 根据电话查询
List<Person> selectByMobile(String s);
// 根据id查询
Person selectById(int id);
}
dao实现
public class PersonDAO implements IPersonDAO{
@Override
public void insertPerson(Person p) {
// TODO Auto-generated method stub
Connection conn=null;
PreparedStatement ps=null;
try{
// 1、创建连接
conn=JDBCUtil.getConn();
// 2、书写带?的sql语句,创建PreparedStatement
String sql="insert into t_person(id,name,mobile,telphone,email,city,birthday)" +
"values (person_seq.nextval,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
// 3、给?赋值
ps.setString(1, p.getName());
ps.setString(2, p.getMobile());
ps.setString(3, p.getTelphone());
ps.setString(4, p.getEmail());
ps.setString(5, p.getCity());
ps.setDate(6, new java.sql.Date(p.getBirthday().getTime()));
ps.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtil.getClose(null, ps, null);
}
}
@Override
public void updatePerson(Person p) {
Connection conn=null;
PreparedStatement ps=null;
try{
// 1、创建连接
conn=JDBCUtil.getConn();
// 2、书写带?的sql语句,创建PreparedStatement
ps=conn.prepareStatement("update t_person set city=? where id=?");
ps.setString(1, p.getCity());
ps.setInt(2, p.getId());
ps.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtil.getClose(null, ps, null);
}
}
@Override
public void deletePerson(int id) {
// TODO Auto-generated method stub
Connection conn=null;
PreparedStatement ps=null;
try{
conn=JDBCUtil.getConn();
ps=conn.prepareStatement("delete from t_person where id = ?");
ps.setInt(1, id);
ps.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtil.getClose(null, ps, null);
}
}
@Override
public List<Person> selectAll() {
// TODO Auto-generated method stub
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
Person p=null;
List<Person> list=new ArrayList<Person>();
try{
conn=JDBCUtil.getConn();
String sql="select id,name,mobile,telphone,email,city,birthday from t_person";
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while (rs.next()){
p=new Person();
p.setId(rs.getInt(1));
p.setName(rs.getString(2));
p.setMobile(rs.getString(3));
p.setTelphone(rs.getString(4));
p.setEmail(rs.getString(5));
p.setCity(rs.getString(6));
p.setBirthday(rs.getDate(7));
list.add(p);
}
}catch(Exception e){
e.printStackTrace();
}finally{
}
return list;
}
@Override
public List<Person> selectByName(String s) {
// TODO Auto-generated method stub
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
Person p=null;
List<Person> list=new ArrayList<Person>();
try{
conn=JDBCUtil.getConn();
String sql="select id,name,mobile,telphone,email,city,birthday from t_person where name like ?";
ps=conn.prepareStatement(sql);
ps.setString(1, "%"+s+"%");
rs=ps.executeQuery();
while (rs.next()){
p=new Person();
p.setId(rs.getInt(1));
p.setName(rs.getString(2));
p.setMobile(rs.getString(3));
p.setTelphone(rs.getString(4));
p.setEmail(rs.getString(5));
p.setCity(rs.getString(6));
p.setBirthday(rs.getDate(7));
list.add(p);
}
}catch(Exception e){
e.printStackTrace();
}finally{
}
return list;
}
@Override
public List<Person> selectByMobile(String s) {
// TODO Auto-generated method stub
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
Person p=null;
List<Person> list=new ArrayList<Person>();
try{
conn=JDBCUtil.getConn();
String sql="select id,name,mobile,telphone,email,city,birthday from t_person where mobile like ?";
ps=conn.prepareStatement(sql);
ps.setString(1, "%"+s+"%");
rs=ps.executeQuery();
while (rs.next()){
p=new Person();
p.setId(rs.getInt(1));
p.setName(rs.getString(2));
p.setMobile(rs.getString(3));
p.setTelphone(rs.getString(4));
p.setEmail(rs.getString(5));
p.setCity(rs.getString(6));
p.setBirthday(rs.getDate(7));
list.add(p);
}
}catch(Exception e){
e.printStackTrace();
}finally{
}
return list;
}
@Override
public Person selectById(int id) {
// TODO Auto-generated method stub
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
Person p=null;
try{
conn=JDBCUtil.getConn();
ps=conn.prepareStatement("select id,name,mobile,telphone,email,city,birthday from t_person where id=?");
ps.setInt(1, id);
rs=ps.executeQuery();
if(rs.next()){
p=new Person();
p.setId(rs.getInt(1));
p.setName(rs.getString(2));
p.setMobile(rs.getString(3));
p.setTelphone(rs.getString(4));
p.setEmail(rs.getString(5));
p.setCity(rs.getString(6));
p.setBirthday(rs.getDate(7));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtil.getClose(conn, ps, rs);
}
return p;
}
}
service接口
public interface TelbookService {
//添加联系人需要使用的业务方法
public void regist(Person person) ;
//修改联系人时需要调用的业务方法
public void changePersonMessage(Person person);
//通过Id查找联系人
public Person getOnePerson(Integer id) ;
//获得所有的联系人的业务方法
public List<Person> getAllPersons() ;
//根据联系人姓名查找联系人的业务方法
public List<Person> getPersonByName(String name);
//根据手机号码查询相关的联系人的业务方法
public List<Person> getPersonByMobile(String mobile);
//删除联系人时需要调用的业务方法
public void dropPerson(Integer id);
}
service实现:
package com.baizhi.laulicy.service;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import com.baizhi.laulicy.dao.IPersonDAO;
import com.baizhi.laulicy.dao.PersonDAO;
import com.baizhi.laulicy.entity.Person;
import com.baizhi.laulicy.util.JDBCUtil;
public class TelbookServiceImpl implements TelbookService{
@Override
public void regist(Person person) {
// TODO Auto-generated method stub
Connection conn=null;
try{
// 1、使用工具类创建连接
conn=JDBCUtil.getConn();
// 2、修改事务的默认行为
conn.setAutoCommit(false);
// 3、调用DAO方法完成业务功能
IPersonDAO ip=new PersonDAO();
ip.insertPerson(person);
// 4、事务提交或回滚
conn.commit();
}catch(Exception e){
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally{
JDBCUtil.getClose(conn, null, null);
}
}
@Override
public void changePersonMessage(Person person) {
// TODO Auto-generated method stub
Connection conn=null;
try{
// 1、使用工具类创建连接
conn=JDBCUtil.getConn();
// 2、修改事务的默认行为
conn.setAutoCommit(false);
// 3、调用DAO方法完成业务功能
IPersonDAO ip=new PersonDAO();
ip.updatePerson(person);
// 4、事务提交或回滚
conn.commit();
}catch(Exception e){
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally{
JDBCUtil.getClose(conn, null, null);
}
}
@Override
public Person getOnePerson(Integer id) {
// TODO Auto-generated method stub
IPersonDAO ip=new PersonDAO();
return ip.selectById(id);
}
@Override
public List<Person> getAllPersons() {
// TODO Auto-generated method stub
IPersonDAO ip=new PersonDAO();
List<Person> list=ip.selectAll();
return list;
}
@Override
public List<Person> getPersonByName(String name) {
// TODO Auto-generated method stub
IPersonDAO ip=new PersonDAO();
List<Person> list=ip.selectByName(name);
return list;
}
@Override
public List<Person> getPersonByMobile(String mobile) {
// TODO Auto-generated method stub
IPersonDAO ip=new PersonDAO();
List<Person> list=ip.selectByMobile(mobile);
return list;
}
@Override
public void dropPerson(Integer id) {
// TODO Auto-generated method stub
Connection conn=null;
try{
conn=JDBCUtil.getConn();
conn.setAutoCommit(false);
IPersonDAO ip=new PersonDAO();
ip.deletePerson(id);
conn.commit();
}catch(Exception e){
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally{
JDBCUtil.getClose(conn, null, null);
}
}
}
测试:
@Test
public void test2(){
PersonDAO dao = new PersonDAOImpl();
List<Person> list = dao.selectAll();
System.out.println(list);
}
}