jDBC工具类和bean结合

来源：互联网发布：强奸致死知乎编辑：程序博客网时间：2024/05/24 15:36

package com.tdb.dao;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement ;import java.sql.Date;/** * 处理数据库的连接和访问 * @author sanware bqlr * @version 1.01 */public class MyOracle {private Connection conn = null;private Statement stmt = null;private PreparedStatement prepstmt = null;//这是一个全局类,里面放置数据库的参数,如数据库主机 访问用户名 密码等private static BeansConstants CONST = BeansConstants.getInstance();/** * 构造数据库的连接和访问类 */public MyOracle() throws Exception {Class.forName(CONST.dbdriver);conn = DriverManager.getConnection(CONST.dburl, CONST.du, CONST.dp);stmt = conn.createStatement();}public MyOracle(String sql) throws Exception {Class.forName(CONST.dbdriver);conn = DriverManager.getConnection(CONST.dburl, CONST.du, CONST.dp);this.prepareStatement(sql);}/** * 返回连接 * @return Connection 连接 */public Connection getConnection() {return conn;}/** * PreparedStatement * @return sql 预设SQL语句 */public void prepareStatement(String sql) throws SQLException {prepstmt = conn.prepareStatement(sql);}/** * 设置对应值 * @param index 参数索引 * @param value 对应值 */public void setString(int index,String value) throws SQLException {prepstmt.setString(index,value);}public void setInt(int index,int value) throws SQLException {prepstmt.setInt(index,value);}public void setBoolean(int index,boolean value) throws SQLException {prepstmt.setBoolean(index,value);}public void setDate(int index,Date value) throws SQLException {prepstmt.setDate(index,value);}public void setLong(int index,long value) throws SQLException {prepstmt.setLong(index,value);}public void setFloat(int index,float value) throws SQLException {prepstmt.setFloat(index,value);}//File file = new File("test/data.txt");//int fileLength = file.length();//InputStream fin = new java.io.FileInputStream(file);//mysql.setBinaryStream(5,fin,fileLength);public void setBinaryStream(int index,InputStream in,int length) throws SQLException {prepstmt.setBinaryStream(index,in,length);}public void clearParameters()throws SQLException{prepstmt.clearParameters();}/** * 返回预设状态 */public PreparedStatement getPreparedStatement() {this.getConnection();return prepstmt;}/** * 返回状态 * @return Statement 状态 */public Statement getStatement() {return stmt;}/** * 执行SQL语句返回字段集 * @param sql SQL语句 * @return ResultSet 字段集 */public ResultSet executeQuery(String sql) throws SQLException {if (stmt != null) {return stmt.executeQuery(sql);}else return null;}public ResultSet executeQuery() throws SQLException {if (prepstmt != null) {return prepstmt.executeQuery();}else return null;}/** * 执行SQL语句 * @param sql SQL语句 */public void executeUpdate(String sql) throws SQLException {if (stmt != null)stmt.executeUpdate(sql);}public void executeUpdate() throws SQLException {if (prepstmt != null)prepstmt.executeUpdate();}/** * 关闭连接 */public void close() throws Exception {if (stmt != null) {stmt.close();stmt = null;}if (prepstmt != null) {prepstmt.close();prepstmt = null;}conn.close();conn = null;}}

package com.tdb.dao;public class BeansConstants {    public static final String dbdriver = "oracle.jdbc.driver.OracleDriver";    public static final String dburl = "jdbc:oracle:thin:@127.0.0.1:1521:tvds";    public static final String du = "tdb";    public static final String dp = "tdb";    public static BeansConstants getInstance() {        // TODO Auto-generated method stub        return new BeansConstants();    }}BeansConstants

package com.tdb.dao;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement ;
import java.sql.Date;

/**
* 处理数据库的连接和访问
* @author sanware bqlr
* @version 1.01
*/
public class MyOracle {

   private Connection conn = null;
   private Statement stmt = null;
   private PreparedStatement prepstmt = null;

//   这是一个全局类,里面放置数据库的参数,如数据库主机访问用户名密码等
   private static BeansConstants CONST = BeansConstants.getInstance();

   /**
   * 构造数据库的连接和访问类
   */
   public MyOracle() throws Exception {
       Class.forName(CONST.dbdriver);
       conn = DriverManager.getConnection(CONST.dburl, CONST.du, CONST.dp);
       stmt = conn.createStatement();
   }
   public MyOracle(String sql) throws Exception {
       Class.forName(CONST.dbdriver);
       conn = DriverManager.getConnection(CONST.dburl, CONST.du, CONST.dp);
       this.prepareStatement(sql);
   }

   /**
   * 返回连接
   * @return Connection 连接
   */
   public Connection getConnection() {
       return conn;
   }
   /**
   * PreparedStatement
   * @return sql 预设SQL语句
   */
   public void prepareStatement(String sql) throws SQLException {
       prepstmt = conn.prepareStatement(sql);
   }
   /**
   * 设置对应值
   * @param index 参数索引
   * @param value 对应值
   */
   public void setString(int index,String value) throws SQLException {
       prepstmt.setString(index,value);
   }
   public void setInt(int index,int value) throws SQLException {
       prepstmt.setInt(index,value);
   }
   public void setBoolean(int index,boolean value) throws SQLException {
       prepstmt.setBoolean(index,value);
   }
   public void setDate(int index,Date value) throws SQLException {
       prepstmt.setDate(index,value);
   }
   public void setLong(int index,long value) throws SQLException {
       prepstmt.setLong(index,value);
   }
   public void setFloat(int index,float value) throws SQLException {
       prepstmt.setFloat(index,value);
   }
//   File file = new File("test/data.txt");
//   int fileLength = file.length();
//   InputStream fin = new java.io.FileInputStream(file);
//   mysql.setBinaryStream(5,fin,fileLength);
   public void setBinaryStream(int index,InputStream in,int length) throws SQLException {
       prepstmt.setBinaryStream(index,in,length);
   }

   public void clearParameters()
   throws SQLException
   {
       prepstmt.clearParameters();
   }
   /**
   * 返回预设状态
   */
   public PreparedStatement getPreparedStatement() {
       this.getConnection();
       return prepstmt;
   }


   /**
   * 返回状态
   * @return Statement 状态
   */
   public Statement getStatement() {
       return stmt;
   }
   /**
   * 执行SQL语句返回字段集
   * @param sql SQL语句
   * @return ResultSet 字段集
   */
   public ResultSet executeQuery(String sql) throws SQLException {
       if (stmt != null) {
           return stmt.executeQuery(sql);
       }
       else return null;
   }
   public ResultSet executeQuery() throws SQLException {
       if (prepstmt != null) {
           return prepstmt.executeQuery();
       }
       else return null;
   }
   /**
   * 执行SQL语句
   * @param sql SQL语句
   */
   public void executeUpdate(String sql) throws SQLException {
       if (stmt != null)
           stmt.executeUpdate(sql);
   }
   public void executeUpdate() throws SQLException {
       if (prepstmt != null)
           prepstmt.executeUpdate();
   }
   /**
   * 关闭连接
   */
   public void close() throws Exception {
       if (stmt != null) {
           stmt.close();
           stmt = null;
       }
       if (prepstmt != null) {
           prepstmt.close();
           prepstmt = null;
       }
       conn.close();
       conn = null;
   }

}

创建BeansConstants 类

package com.tdb.dao;public class BeansConstants {public static final String dbdriver = "oracle.jdbc.driver.OracleDriver";public static final String dburl = "jdbc:oracle:thin:@127.0.0.1:1521:tvds";public static final String du = "tdgb";public static final String dp = "tdgb";public static BeansConstants getInstance() {// TODO Auto-generated method stubreturn new BeansConstants();}}

创建bean类

package com.tdb.dao;import java.sql.ResultSet;public class DeviceBean {private String deviceNo;private String deviceModel;private String deviceScope;private String deviceAddress;private String radminUserName;private String radminPass;private String radminAddress;public String getDeviceNo() {return deviceNo;}public void setDeviceNo(String deviceNo) {this.deviceNo = deviceNo;}public String getDeviceModel() {return deviceModel;}public void setDeviceModel(String deviceModel) {this.deviceModel = deviceModel;}public String getDeviceScope() {return deviceScope;}public void setDeviceScope(String deviceScope) {this.deviceScope = deviceScope;}public String getDeviceAddress() {return deviceAddress;}public void setDeviceAddress(String deviceAddress) {this.deviceAddress = deviceAddress;}public String getRadminUserName() {return radminUserName;}public void setRadminUserName(String radminUserName) {this.radminUserName = radminUserName;}public String getRadminPass() {return radminPass;}public void setRadminPass(String radminPass) {this.radminPass = radminPass;}public String getRadminAddress() {return radminAddress;}public void setRadminAddress(String radminAddress) {this.radminAddress = radminAddress;}private String deviceBase_insert="insert into deviceBase values (?,?,?,?,?,?,?)";public void insertnavlink() throws Exception{try{ResultSet rs=null;MyOracle myoracle = new MyOracle(deviceBase_insert);myoracle.setString(1, this.deviceNo);myoracle.setString(2, this.deviceModel);myoracle.setString(3, this.deviceScope);myoracle.setString(4, this.deviceAddress);myoracle.setString(5, this.radminUserName);myoracle.setString(6, this.radminPass);myoracle.setString(7, this.radminAddress);myoracle.executeUpdate();                        myoracle.close();                        myoracle = null;} catch (Exception ex) {throw new Exception("insertnavlink()"+ex.getMessage());}}}

在Jsp中,就可以直接使用一句语句使用insertnavlink()了:

<jsp:useBean id="NAV" scope="session" class="mysite.Navlink" />
<jsp:setProperty name="NAV" property="*" />
........
NAV.insertnavlink();
......