jdbc

DB包装类：

package com.hz.bbs.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DB {
public static Connection getConn() {
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
try {
conn=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/background?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8",
"root", "root");
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static Statement getStmt(Connection conn) {
Statement stmt=null;
try {
stmt=conn.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}
public static PreparedStatement getPstmt(Connection conn ,String sql) {
PreparedStatement pstmt=null;
try {
pstmt=conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pstmt;
}
public static PreparedStatement getPstmt(Connection conn,String sql,int autoGeneratedKeys) {
PreparedStatement pstmt=null;
try {
pstmt=conn.prepareStatement(sql, autoGeneratedKeys);
} catch (SQLException e) {
e.printStackTrace();
}
return pstmt;
}
public static int executeUpdate(Connection conn,String sql) {
Statement stmt=null;
int res=0;
try {
stmt=conn.createStatement();
res=stmt.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return res;
}
public static ResultSet getRs(Statement stmt, String sql) {
ResultSet rs=null;
try {
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}


}
运用包装类，实现增删改查

package com.hz.bbs.dao.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;

import com.hz.bbs.dao.Dao;
import com.hz.bbs.entity.Board;
import com.hz.bbs.entity.JavaWeb;
import com.hz.bbs.entity.Types;
import com.hz.bbs.entity.User;
import com.hz.bbs.util.DB;

 

public class Daoimpl implements Dao {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
PreparedStatement pstmt;

/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#select(com.hz.bbs.entity.User)
* 登陆用户的查询
*/

@Override
public int select(User user) throws Exception {
int rowcounts = 0;
conn= DB.getConn();
String sql ="select count(*)from bbs_user where username='"+user.getUsername()+"' and userpassword='"+user.getUserpassword()+"' and ischecked="+user.getIschecked();
System.out.println(sql);
stmt=DB.getStmt(conn);
rs= stmt.executeQuery(sql);
while(rs.next()){
rowcounts=rs.getInt("count(*)");//得到查询出的记录 有就为1 反之为0
System.out.println("int:"+rowcounts);
}
return rowcounts;




}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#insert(com.hz.bbs.entity.Board)
* 版面信息增加
*/
@Override
public void insert(Board board) throws Exception {

try{
conn=DB.getConn();
String sql="insert into bbs_board(board_classID,board_name,board_master,board_pcard) values(?,?,?,?)";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, board.getBoard_classID());
pstmt.setString(2, board.getBoard_name());
pstmt.setString(3,board.getBoard_master());
pstmt.setString(4,board.getBoard_pcard());
pstmt.executeUpdate();
}catch(Exception e) {
e.printStackTrace();
}
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#selectBoard(java.util.Map)
* 版面信息联合分页查询
*/

@Override
public List<Board> selectBoard(Map<String, Serializable> map) throws Exception {
conn=DB.getConn();
String sql="select board_id,board_classID,board_name,board_master,board_pcard,b.type_value board_className from bbs_board a left join bbs_type b on a.board_classID = b.type_key limit " + map.get("startNum") + "," + map.get("endNum") ;
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
List<Board> newBoardList = new ArrayList<Board>();
while(rs.next()) {
Board board = new Board();
board.setBoard_id(rs.getInt("board_id"));
board.setBoard_classID(rs.getInt("board_classID"));
board.setBoard_className(rs.getString("board_className"));
board.setBoard_name(rs.getString("board_name"));
board.setBoard_master(rs.getString("board_master"));
board.setBoard_pcard(rs.getString("board_pcard"));
System.out.println(rs.getString(5));
newBoardList.add(board);
}
System.out.println("总共"+newBoardList.size()+"条记录");
rs.close();
stmt.close();
conn.close();
return newBoardList;
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#delect(com.hz.bbs.entity.Board)
* 根据ID删除版面信息
*/
@Override
public int delect(Board board) throws Exception {
conn=DB.getConn();
stmt=conn.createStatement();
String sql = "delete from bbs_board where board_id=" + board.getBoard_id();
System.out.println("123"+ board.getBoard_id());
int d = stmt.executeUpdate(sql);
if(d==0) {
System.out.println("删除失败");
}

return d;
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#updateBoard(com.hz.bbs.entity.Board)
* 根据ID查询版面信息
*/
@Override
public List<Board> updateBoard(Board board) throws Exception {
conn=DB.getConn();
stmt=conn.createStatement();
String sql = "select * from bbs_board where board_id="+board.getBoard_id();
rs = stmt.executeQuery(sql);
List<Board> list = new ArrayList<Board>();
while(rs.next()) {
board = new Board();
board.setBoard_id(rs.getInt("board_id"));
board.setBoard_classID(rs.getInt("board_classID"));
board.setBoard_name(rs.getString("board_name"));
board.setBoard_master(rs.getString("board_master"));
board.setBoard_pcard(rs.getString("board_pcard"));
System.out.println(rs.getString(3));
list.add(board);

}
System.out.println("总共"+list.size()+"条记录");
rs.close();
stmt.close();
conn.close();
return list;
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#updateboard(com.hz.bbs.entity.Board)
* 根据ID更新版面信息
*/
@Override
public void updateboard(Board board) throws Exception {
conn=DB.getConn();
stmt=conn.createStatement();
String sql="update bbs_board set board_name='"+board.getBoard_name()+"',board_master='"+board.getBoard_master()+"',board_pcard='"+board.getBoard_pcard()+"' where board_id="+board.getBoard_id();
System.out.println("456"+board.getBoard_master());
System.out.println("789"+board.getBoard_pcard());
stmt.executeUpdate(sql);
stmt.close();
conn.close();
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#selectType()
* 查询type表信息
*/
@Override
public List<Types> selectType() throws Exception {
conn=DB.getConn();
stmt=conn.createStatement();
String sql ="select * from bbs_type ";
rs = stmt.executeQuery(sql);
List<Types> list = new ArrayList<Types>();
while(rs.next()) {
Types types= new Types();
types.setType_id(rs.getInt("type_id"));
types.setType_key(rs.getInt("type_key"));
types.setType_value(rs.getString("type_value"));
System.out.println(rs.getString(3));
System.out.println(rs.getString("type_value"));
list.add(types);
}
System.out.println("总共"+list.size()+"条记录");
return list;
}

/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#searchForCount(java.util.Map)
* 查询出版面信息的总记录数
*/

@Override
public int searchForCount(Map<String, Serializable> map)throws SQLException {
int total = 0;
conn=DB.getConn();
stmt=conn.createStatement();
String sql=" select count(*) from bbs_board ";
rs= stmt.executeQuery(sql);
while(rs.next()){
total=rs.getInt("count(*)");//得到查询出的记录数
System.out.println("int:"+total);
}
return total;
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#searchForType(java.util.Map)
* 查询出type表的记录数
*/
@Override
public int searchForType(Map<String, Serializable> map) throws Exception {
int total=0;
conn=DB.getConn();
stmt=conn.createStatement();
String sql="select count(*) from bbs_type";
rs=stmt.executeQuery(sql);
while(rs.next()) {
total = rs.getInt("count(*)");
System.out.println("type:"+total);
}
return total;
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#searchForTypeLimit(java.util.Map)
* 分页查询出type表的信息
*/
@Override
public List<Types> searchForTypeLimit(Map<String, Serializable> map)
throws Exception {
conn =DB.getConn();
stmt=conn.createStatement();
String sql="select * from bbs_type limit " + map.get("startNum") + "," + map.get("endNum") ;
rs=stmt.executeQuery(sql);
List<Types> newTypeList = new ArrayList<Types>();
while(rs.next()) {
Types types = new Types();
types.setType_id(rs.getInt("type_id"));
types.setType_key(rs.getInt("type_key"));
types.setType_value(rs.getString("type_value"));
newTypeList.add(types);

}
System.out.println("总共"+newTypeList.size()+"条记录");
return newTypeList;
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#delect(com.hz.bbs.entity.Types)
* 根据ID删除type表的信息
*/
@Override
public int delect(Types types) throws Exception {
conn = DB.getConn();
stmt = conn.createStatement();
String sql = "delete from bbs_type where type_id = " +types.getType_id();
System.out.println(types.getType_id());
int d = stmt.executeUpdate(sql);
if(d==0) {
System.out.println("删除失败");
}
return d;
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#updateType(com.hz.bbs.entity.Types)
* 根据ID查询出type表的信息
*/
@Override
public List<Types> updateType(Types types) throws Exception {
conn = DB.getConn();
stmt = conn.createStatement();
String sql = "select * from bbs_type where type_id= "+types.getType_id();
rs = stmt.executeQuery(sql);
List<Types> listType = new ArrayList<Types>();
while(rs.next()) {
types = new Types();
types.setType_id(rs.getInt("type_id"));
types.setType_key(rs.getInt("type_key"));
types.setType_value(rs.getString("type_value"));
listType.add(types);
}
return listType;
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#upadateType(com.hz.bbs.entity.Types)
* 根据ID更新type表的信息
*/

@Override
public void upadateType(Types types) throws Exception {
conn=DB.getConn();
stmt=conn.createStatement();
String sql="update bbs_type set type_key= "+types.getType_key()+",typevalue='"+types.getType_value()+"' where type_id="+types.getType_id();
System.out.println("456"+types.getType_key());
System.out.println("789"+types.getType_value());
stmt.executeUpdate(sql);
stmt.close();
conn.close();
}
/*
* (non-Javadoc)
* @see com.hz.bbs.dao.Dao#inserType(com.hz.bbs.entity.Types)
* 添加type表的信息
*/
@Override
public void inserType(Types type) throws Exception {
conn = DB.getConn();
String sql ="insert into bbs_type(type_key,type_value) values(?,?)";
pstmt =conn.prepareStatement(sql);
pstmt.setInt(1, type.getType_key());
pstmt.setString(2, type.getType_value());
pstmt.executeUpdate();
pstmt.close();
conn.close();
}
@Override
public List<Board> selectBoardId(Board boardId)
throws Exception {
conn=DB.getConn();
String sql="select board_classID,board_name,board_master,board_pcard,board_title,board_cont,b.type_value board_className from bbs_board a left join bbs_type b on a.board_classID = b.type_key where board_id= "+boardId.getBoard_id();
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
List<Board> newBoardListId = new ArrayList<Board>();
while(rs.next()) {
Board board = new Board();
//board.setBoard_id(rs.getInt("board_id"));
board.setBoard_classID(rs.getInt("board_classID"));
board.setBoard_className(rs.getString("board_className"));
board.setBoard_name(rs.getString("board_name"));
board.setBoard_master(rs.getString("board_master"));
board.setBoard_pcard(rs.getString("board_pcard"));
board.setBoard_cont(rs.getString("board_cont"));
board.setBoard_title(rs.getString("board_title"));
System.out.println(rs.getString(5));
newBoardListId.add(board);
}
System.out.println("总共"+newBoardListId.size()+"条记录");
rs.close();
stmt.close();
conn.close();
return newBoardListId;
}