BaseDao数据库连接公共方法:
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDao {
private final String DRIVER_CLASS="com.mysql.jdbc.Driver";
private final String URL="jdbc:mysql://127.0.0.1:3306/lx";
private final String USER_NAME="root";
private final String PASSWORD="123";
protected Connection conn;
protected PreparedStatement pt;
protected ResultSet res;
public Connection getConn(){
try {
Class.forName(DRIVER_CLASS);
conn=DriverManager.getConnection(URL,USER_NAME,PASSWORD);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public void close(){
try {
if(res!=null)
res.close();
if(pt!=null)
pt.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Dao层接口:
package dao;
import bean.MyUser;
public interface MyUserDao {
public void addUser(MyUser myuser);
public MyUser login(String uname,String upwd);
}
Dao层接口实现类:
package dao.impl;
import java.sql.SQLException;
import bean.MyUser;
import dao.BaseDao;
import dao.MyUserDao;
public class MyUserDaoImpl extends BaseDao implements MyUserDao{
public void addUser(MyUser user) {
try {
conn=getConn();
String sql="insert into myuser values(null,?,?,?,?)";
pt=conn.prepareStatement(sql);
pt.setString(1,user.getUname());
pt.setString(2, user.getUpwd());
pt.setString(3, user.getTureName());
pt.setInt(4, user.getPhone());
if(pt.executeUpdate()>=2){
System.out.println("注册成功!");
}else{
System.out.println("注册失败!");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
}
public MyUser login(String uname, String upwd) {
try {
conn=getConn();
String sql="Select*from myuser where uname=? and upwd=?";
pt=conn.prepareStatement(sql);
pt.setString(1, uname);
pt.setString(2, upwd);
res=pt.executeQuery();
if(res.next()){
MyUser user=new MyUser(res.getInt(1),res.getString(2),res.getString(3),res.getString(4),res.getInt(5));
return user;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return null;
}
}
复杂的实例代码:
package com.soda.dao.impl;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.soda.dao.JfbAfficheDao;
import com.soda.entity.News;
import com.soda.entity.Notice;
import com.soda.util.BaseDao;
import com.soda.util.JfbPageInfo;
public class JfbAfficheDaoImpl extends BaseDao implements JfbAfficheDao {
public int getTotalRows(Notice nt) {
try {
getConn();
StringBuffer buffer=new StringBuffer("select count(1) from notice where not_delete=1 ");
if(nt.getNotTitle()!=null&&!"".equals(nt.getNotTitle())){
buffer.append("and not_title like ? ");
}
if(nt.getNotUpdatetime()!=null&&!"".equals(nt.getNotUpdatetime())){
buffer.append("and not_updatetime=? ");
}
if(nt.getNotState()>-1){
buffer.append("and not_state=? ");
}
pt=conn.prepareStatement(buffer.toString());
int x=1;
if(nt.getNotTitle()!=null&&!"".equals(nt.getNotTitle())){
pt.setString(x,nt.getNotTitle()+"%");
x++;
}
if(nt.getNotUpdatetime()!=null&&!"".equals(nt.getNotUpdatetime())){
pt.setString(x, nt.getNotUpdatetime());
x++;
}
if(nt.getNotState()>-1){
pt.setInt(x, nt.getNotState());
}
res=pt.executeQuery();
if(res.next()){
return res.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return 0;
}
public List<Notice> getNewsByCondition(Notice nt, JfbPageInfo pi) {
try {
getConn();
StringBuffer buffer=new StringBuffer("select * from notice where 1=1 and not_delete=1 ");
if(nt.getNotTitle()!=null&&!"".equals(nt.getNotTitle())){
buffer.append("and not_title=? ");
}
if(nt.getNotUpdatetime()!=null&&!"".equals(nt.getNotUpdatetime())){
buffer.append("and not_updatetime=? ");
}
if(nt.getNotState()>-1){
buffer.append("and not_state=? ");
}
buffer.append("order by not_id desc limit ?,? ");
pt=conn.prepareStatement(buffer.toString());
int x=1;
if(nt.getNotTitle()!=null&&!"".equals(nt.getNotTitle())){
pt.setString(x,nt.getNotTitle()+"%");
x++;
}
if(nt.getNotUpdatetime()!=null&&!"".equals(nt.getNotUpdatetime())){
pt.setString(x, nt.getNotUpdatetime());
x++;
}
if(nt.getNotState()>-1){
pt.setInt(x, nt.getNotState());
x++;
}
pt.setInt(x,(pi.getPageIndex()-1)*pi.getPageRows());
x++;
pt.setInt(x, pi.getPageRows());
res=pt.executeQuery();
List<Notice> notices=new ArrayList<Notice>();
while(res.next()){
Notice nti=new Notice(res.getInt(1),res.getString(2),res.getString(3),res.getInt(4),res.getString(5),res.getString(6),res.getInt(7),res.getInt(8));
notices.add(nti);
}
return notices;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return null;
}
public Notice getNewsById(int notId) {
try {
getConn();
String sql="select*from notice where not_id=?";
pt=conn.prepareStatement(sql);
pt.setInt(1,notId);
res=pt.executeQuery();
if(res.next()){
Notice nt=new Notice(res.getInt(1),res.getString(2),res.getString(3),res.getInt(4),res.getString(5),res.getString(6),res.getInt(7),res.getInt(8));
return nt;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return null;
}
public int editNewsById(Notice noti) {
try {
getConn();
String sql="update notice set not_title=?,not_author=?,not_level=?,not_content=? where not_id=? ";
pt=conn.prepareStatement(sql);
pt.setString(1, noti.getNotTitle());
pt.setString(2, noti.getNotAuthor());
pt.setInt(3, noti.getNotLevel());
pt.setString(4,noti.getNotContent());
pt.setInt(5, noti.getNotId());
return pt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return 0;
}
public int deleteNoticeById(int notId) {
try {
getConn();
String sql="update notice set not_delete=0 where not_id=? ";
pt=conn.prepareStatement(sql);
pt.setInt(1, notId);
return pt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return 0;
}
public int ckdeleteByNotIds(String[] notIds) {
try {
getConn();
StringBuffer buffer=new StringBuffer("update notice set not_delete=0 where not_id in ( ");
for(int i=0;i<notIds.length;i++){
buffer.append(" ?, ");
}
buffer.deleteCharAt(buffer.length()-1);
buffer.append(" ) ");
pt=conn.prepareStatement(buffer.toString());
int index=0;
for(int i=0;i<notIds.length;i++){
pt.setInt(i+1, Integer.parseInt(notIds[index++]));
}
return pt.executeUpdate();
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return 0;
}
public List<Notice> getNoticeByNotIds(String[] notIds) {
try {
getConn();
StringBuffer buffer=new StringBuffer("select*from notice where not_delete=1 and not_id in(");
for(int i=0;i<notIds.length;i++){
buffer.append(" ?,");
}
buffer.deleteCharAt(buffer.length()-1);
buffer.append(")");
pt=conn.prepareStatement(buffer.toString());
int index=0;
for(int i=0;i<notIds.length ;i++){
pt.setInt(i+1, Integer.parseInt(notIds[index++]));
}
res=pt.executeQuery();
List<Notice> notis=new ArrayList<Notice>();
while(res.next()){
Notice noti=new Notice(res.getInt(1),res.getString(2),res.getString(3),res.getInt(4),res.getString(5),res.getString(6),res.getInt(7),res.getInt(8));
notis.add(noti);
}
return notis;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return null;
}
public int add(Notice noti) {
try {
getConn();
String sql="insert into notice values(null,?,?,?,?,now(),1,1)";
pt=conn.prepareStatement(sql);
pt.setString(1,noti.getNotTitle());
pt.setString(2,noti.getNotAuthor());
pt.setInt(3,noti.getNotLevel());
pt.setString(4,noti.getNotContent());
return pt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return 0;
}
}