增删改的工具类
package com.librarySystem;
import java.sql.*;
public class JDBCUTILS {
private static final String DRIVER = "oracle.jdbc.OracleDriver";
private static String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String USER = "cc";
private static String PASSWORD = "ccpassword";
static {
try {
Class.forName(DRIVER);
}catch(Exception e){
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
return connection;
}catch(Exception ex){
ex.printStackTrace();
}
return null;
}
public static void close(ResultSet resultSet, Statement statement, Connection connection){
try
{
if (resultSet != null && !resultSet.isClosed())
resultSet.close();
}
catch(SQLException ex)
{
ex.printStackTrace();
}
finally
{
try
{
if (statement != null && !statement.isClosed())
statement.close();
}
catch (SQLException ex)
{
ex.printStackTrace();
}
finally
{
try
{
if (connection != null && !connection.isClosed())
connection.close();
}
catch(SQLException ex)
{
ex.printStackTrace();;
}
}
}
}
public static void close(Statement statement, Connection connection){
close(null,statement,connection);
}
}
增删查改的公共类
package com.librarySystem;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public abstract class CommonDao<T> extends JDBCUTILS {
public static int executeUpdate(String sql,Object ...params){
Connection conn =null;
PreparedStatement preparedStatement =null;
int i=0;
try {
conn = getConnection();
preparedStatement = conn.prepareStatement(sql);
if(params!=null && params.length>0){
for(int j=0;j<params.length;j++){
preparedStatement.setObject(j+1,params[j]);
}
}
i = preparedStatement.executeUpdate();
}catch(SQLException e){
e.printStackTrace();
}finally {
close(preparedStatement,conn);
}
return i;
}
public List<T> queryBeenList(String sql,Object ...paramas){
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
List<T>lst =new ArrayList<>();
T tmp =null;
try{
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
if(paramas!=null&¶mas.length>0){
for(int i=0;i<paramas.length;i++){
preparedStatement.setObject(i+1,paramas[i]);
}
}
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
tmp = getBeanFromResultSet(resultSet);
lst.add(tmp);
}
}catch(SQLException e){
e.printStackTrace();
}finally {
close(resultSet,preparedStatement,connection);
}
return lst;
}
public T queryBeen(String sql,Object ...params){
List<T>lst= queryBeenList(sql,params);
if(lst.size()>0){
return lst.get(0);
}
return null;
}
public abstract T getBeanFromResultSet(ResultSet resultSet) throws SQLException;
}
应用
package com.librarySystem;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
class Reader{
private String rno;
private String rname;
private String rsex;
private int rage;
private String rboss;
private String raddress;
public String getRno() {
return rno;
}
public String getRname() {
return rname;
}
public String getRsex() {
return rsex;
}
public int getRage() {
return rage;
}
public String getRaddress() {
return raddress;
}
public String getRboss() {
return rboss;
}
public void setRno(String rno) {
this.rno = rno;
}
public void setRname(String rname) {
this.rname = rname;
}
public void setRsex(String rsex) {
this.rsex = rsex;
}
public void setRage(int rage) {
this.rage = rage;
}
public void setRboss(String rboss) {
this.rboss = rboss;
}
public void setRaddress(String raddress) {
this.raddress = raddress;
}
public String toString(){
return rno+" "+rname+" "+rsex+" "+rage+" "+rboss+" "+raddress;
}
}
public class ConnnectDatabase extends CommonDao<Reader> {
public static int doInsert(Reader reader) {
String sql ="Insert into reader values('R011','George','男',?,null,?)";
return executeUpdate(sql,reader.getRage(),reader.getRaddress());
}
public static int doDelete(String rno) {
String sql ="Delete from reader where rno=?";
return executeUpdate(sql,rno);
}
public static int doUpdate(Reader reader) {
String sql="Update reader set rname=?,rage=? where rno=?";
return executeUpdate(sql,reader.getRname(),reader.getRage(),reader.getRno());
}
public static void main(String args[]) {
System.out.print(new ConnnectDatabase().queryById("R011"));
}
@Override
public Reader getBeanFromResultSet(ResultSet resultSet) throws SQLException {
Reader reader= new Reader();
reader.setRno(resultSet.getString("RNO"));
reader.setRname(resultSet.getString("RNAME"));
reader.setRsex( resultSet.getString("RSEX"));
reader.setRage(resultSet.getInt("RAGE"));
reader.setRboss(resultSet.getString("RBOSS"));
reader.setRaddress(resultSet.getString("RADDRESS"));
return reader;
}
public List<Reader>queryAll(){
String sql ="Select * from reader";
return queryBeenList(sql);
}
public Reader queryById(String rno){
String sql="Select * from reader where rno=?";
return queryBeen(sql,rno);
}
}