一、
从
http://hsqldb.sourceforge.net/
下载
HSQL1.7.3,
将其中的
hsqldb.jar
复制到你的
web
应用的
WEB-INF/lib
目录下。写一个属性文件
lightningboard.properties
,数据将存放在你的
web
应用
TestHsql/hsqldb/lb_db
下。
#---Database--- DB.DRIVER=org.hsqldb.jdbcDriver DB.URL=jdbc:hsqldb:webapps/TestHsql/hsqldb/lb_db DB.USER=sa DB.PASSWORD= DB.MAX_CONNECTIONS=30
二、写配置文件用来读上面的数据库配置
package lightningboard; import java.util.Properties; import java.io.InputStream; import java.io.IOException; /** * LightningBoard configuration, configuration file: * "/lightningboard.properties". * @version 0.3.5 * @author Xiaobo Liu */
public class Configuration { private Properties properties; private final static Configuration cfg = new Configuration(); private Configuration() { properties = new Properties(); InputStream is = null; try { is = getClass().getResourceAsStream("lightningboard.properties"); properties.load(is); } catch (Exception exception) { System.out.println("Can't read the properties file. "); } finally { try { if (is != null) is.close(); } catch (IOException exception) { // ignored } } } public static Configuration getInstance() { return cfg; } public String getValue(String key) { return properties.getProperty(key); } }
三、数据库连接管理器,从一个简单的连接池中获取连接。
package lightningboard.db; import java.util.Vector; import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException; import lightningboard.Configuration; /** * Database Connection Manager * @version 0.3.5 * @author Xiaobo Liu */ public class DBConnectionManager { private final static DBConnectionManager instance=new DBConnectionManager(); private DBConnectionPool pool; /** * Use singleton pattern, only return one instance of DBConnectionManager. * @return DBConnectionManager */ public static DBConnectionManager getInstance() { return instance; } /** * Get a connection * @return Connection * @throws SQLException this method */ public Connection getConnection() throws SQLException{ return pool.getConnection(); } /** * Free a connection * @param con connection * @throws SQLException this method */ public void freeConnection(Connection con) throws SQLException{ pool.freeConnection(con); } private DBConnectionManager() { init(); } private void init() { Configuration cfg=Configuration.getInstance(); String db_driver=cfg.getValue("DB.DRIVER"); String db_url=cfg.getValue("DB.URL"); String db_user=cfg.getValue("DB.USER"); String db_password=cfg.getValue("DB.PASSWORD"); int db_maxConn=Integer.parseInt(cfg.getValue("DB.MAX_CONNECTIONS")); try { Class.forName(db_driver); } catch (ClassNotFoundException ex) { System.out.println(ex); } pool = new DBConnectionPool(db_url,db_user,db_password,db_maxConn); } // inner class class DBConnectionPool { private Vector freeConnections = new Vector(); private int maxConn; private int connNumb; private String URL; private String password; private String user; public DBConnectionPool(String URL, String user, String password,int maxConn) { this.URL = URL; this.user = user; this.password = password; this.maxConn = maxConn; } public synchronized void freeConnection(Connection con) { freeConnections.addElement(con); connNumb--; notifyAll(); } public synchronized Connection getConnection() throws SQLException{ Connection con = null; if (freeConnections.size() > 0) { con = (Connection) freeConnections.firstElement(); freeConnections.removeElementAt(0); try { if (con.isClosed()) { con = getConnection(); } } catch (SQLException e) { con = getConnection(); } } else if (maxConn == 0 || connNumb < maxConn) { con = newConnection(); } if (con != null) { connNumb++; } return con; } private Connection newConnection() throws SQLException{ Connection con =DriverManager.getConnection(URL,user, password); return con; } } }
四、下面是测试用的
jsp
文件:
Testhsql.jsp,Testhsql1.jsp(
请下载)
<%@ page contentType="text/html; charset=GBK" %> <%@ page language="java" import="java.sql.*"%> <%@ page import="lightningboard.db.DBConnectionManager" %> <% DBConnectionManager dbcm = DBConnectionManager.getInstance(); Connection conn=null; PreparedStatement ps = null; ResultSet rs = null; String sql = null; try { conn=dbcm.getConnection(); //
如果存在表
user
,删除
sql="DROP TABLE IF EXISTS user"; ps = conn.prepareStatement(sql); ps.executeUpdate(); //
创建表
sql = "CREATE TABLE USER(ID INTEGER,NAME VARCHAR(50),MOBILE CHAR(12))"; ps = conn.prepareStatement(sql); ps.executeUpdate(); //
插入数据
sql = "INSERT INTO user VALUES(?,?,?)"; ps = conn.prepareStatement(sql); ps.setInt(1, 1); ps.setString(2, "
张三
"); ps.setString(3,"13912345678"); ps.executeUpdate(); //
查询数据
ps = conn.prepareStatement("SELECT * FROM user"); rs = ps.executeQuery(); while(rs.next()){ out.println("ID:"+rs.getInt("id")+"<br>"); out.println("
姓名
:"+rs.getString("name")+"<br>"); out.println("
手机号
:"+rs.getString("mobile")); } } finally{ if(rs!=null) rs.close(); if(ps!=null) ps.close(); if(conn!=null) conn.close(); } %>
转载请注明原文地址: https://ibbs.8miu.com/read-9971.html