jdbc封装的类

JDBCUtil,java

package cn.qst.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 JDBCUtil {
private static final String DBDRIVER = "com.mysql.jdbc.Driver";

private static final String DBURL = "jdbc:mysql://localhost:3306/studyweb?useUnicode=true&characterEncoding=utf-8&useSSL=false";

private static final String DBUSER = "root";

private static final String DBPASSWORD = "root";
/**
* 获取数据库连接
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
//加载驱动
Class.forName(DBDRIVER);
//创建数据库连接
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}

/**
* 关闭数据连接类
* 连接 预编译命令 、 执行命令 执行结果集
*/
public static void closeAll(Connection conn,PreparedStatement pstmt,Statement stmt,ResultSet rs){
try {
if(rs!=null){
rs.close();
}
if(stmt!=null){
stmt.close();
}
if(pstmt!=null){
pstmt.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

***********************************************************************************

用以上JDBCUtil.java进行实现增删改查基本操作,例子如下

dao中PublicNotice 接口的定义

package cn.qst.dao;

import java.util.List;

import cn.qst.vo.Notice;

public interface PublicNotice {
int insertnotice(Notice notice);
List<Notice> displaypublicnotice();
int deletenotice(int noticeId);
int updatenotice(Notice notice);
Notice displayEditnotice(int noticeId);
}

dao中接口PublicNotice 方法的实现

package cn.qst.dao.impl;

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.List;

import cn.qst.dao.PublicNotice;
import cn.qst.util.JDBCUtil;
import cn.qst.vo.Notice;

public class PublicNoticeDaoImpl extends JDBCUtil implements PublicNotice {
private Connection conn=null;
private PreparedStatement pstmt=null;
private Statement stmt=null;
private ResultSet rs=null;

@Override
public int insertnotice(Notice notice) {
int count=0;
String sql="insert into Notice(title,context,publicerId,publicer,writeDate) values(?,?,?,?,?)";
conn=super.getConnection();
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,notice.getTitle());
pstmt.setString(2, notice.getContext());
pstmt.setInt(3,notice.getPublicerId());
pstmt.setString(4, notice.getPublicer());
pstmt.setTimestamp(5, notice.getWriteDate());
count=pstmt.executeUpdate();
} catch (SQLException e) {

e.printStackTrace();
}

return count;
}

@Override
public List<Notice> displaypublicnotice() {
List<Notice> list=new ArrayList<Notice>();
Notice notice=null;
String sql="select noticeId,title,context,publicerId,publicer,writeDate from notice";
conn=super.getConnection();
try {
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
notice=new Notice();
notice.setNoticeId(rs.getInt("noticeId"));
notice.setTitle(rs.getString("title"));
notice.setContext(rs.getString("context"));
notice.setPublicerId(rs.getInt("publicerId"));
notice.setPublicer(rs.getString("publicer"));
notice.setWriteDate(rs.getTimestamp("writeDate"));
list.add(notice);
}
} catch (SQLException e) {

e.printStackTrace();
}finally{ // 7。关闭连接
super.closeAll(conn, pstmt, stmt, rs);
}

return list;
}

@Override
public int deletenotice(int noticeId) {
int count=0;
String sql="delete from notice where noticeId=?";
conn=super.getConnection();
try {
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, noticeId);
count=pstmt.executeUpdate();
} catch (SQLException e) {

e.printStackTrace();
}finally{ // 7。关闭连接
super.closeAll(conn, pstmt, stmt, rs);
}

return count;
}

@Override
public int updatenotice(Notice notice) {
int count=0;
String sql="update notice set title=?,context=?,writeDate=? where noticeId=?";
conn=super.getConnection();
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,notice.getTitle());
pstmt.setString(2, notice.getContext());
pstmt.setTimestamp(3, notice.getWriteDate());
pstmt.setInt(4,notice.getNoticeId());
count=pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
super.closeAll(conn, pstmt, stmt, rs);
}
return count;
}

@Override
public Notice displayEditnotice(int noticeId) {
Notice notice=null;
String sql="select noticeId,title,context,publicerId,publicer,writeDate from notice where noticeId=?";
conn=super.getConnection();
try {
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, noticeId);
rs=pstmt.executeQuery();
while(rs.next()){
notice=new Notice();
notice.setNoticeId(rs.getInt("noticeId"));
notice.setTitle(rs.getString("title"));
notice.setContext(rs.getString("context"));
notice.setPublicerId(rs.getInt("publicerId"));
notice.setPublicer(rs.getString("publicer"));
notice.setWriteDate(rs.getTimestamp("writeDate"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
super.closeAll(conn, pstmt, stmt, rs);
}

return notice;
}

}

上一篇:awk与cut在以空格为分割域时的区别


下一篇:【Python】excel读写操作 xlrd & xlwt