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 stub
return 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();
......