#1,数据库连接
##1)dbcpconfig.properties
dbcpconfig.properties文件为使用jdbc连接数据库,具体参见:数据库连接
##2)DBpool.java
package com.luo.dal;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
/**
* 使用数据库连接池加大响应速度
*
*/
public class DBPool {
private static DataSource ds;
private static Connection con = null;
PreparedStatement prestmt = null;
ResultSet rs = null;
public static Connection getConnections() {
con = null;
try {
con = ds.getConnection();
} catch (SQLException e) {
System.out.println("获取数据库连接失败....");
e.printStackTrace();
}
return con;
}
/**
* 建立数据库连接 0成功 1失败
*/
public int Connect() {
try {
if (con != null && con.isClosed() == false)
return 0;
try {
InputStream in = DBPool.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties pro = new Properties();
pro.load(in);
ds = BasicDataSourceFactory.createDataSource(pro);
con = ds.getConnection();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return 0;
} catch (Exception e) {
e.printStackTrace();
return 1;
}
}
/**
* 查询 SQL语句执行方法
*
* @throws SQLException
* @sqlstr SQL语句
* @params 参数
**/
public ResultSet QuerySql(String sqlstr, Object... params) throws SQLException {
// Create and execute an SQL statement that returns some data.
prestmt = con.prepareStatement(sqlstr);
for (int i = 0; i < params.length; i++) {
prestmt.setObject(i + 1, params[i]);
}
rs = prestmt.executeQuery();
// Iterate through the data in the result set and display it
return rs;
}
/**
* 增删改 SQL语句执行方法
*
* @throws SQLException
* @sqlstr SQL语句
* @params 参数
**/
public int NonquerySql(String sqlstr, Object... params) throws SQLException {
prestmt = con.prepareStatement(sqlstr);
for (int i = 0; i < params.length; i++) {
prestmt.setObject(i + 1, params[i]);
}
return prestmt.executeUpdate();
}
public static void close(Connection con) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 关闭数据库连接
*
* @throws SQLException
*/
public void Close() {
try {
if (rs != null) {
rs.close();
}
if (prestmt != null) {
prestmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
##3)SQL.java
package com.luo.dal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SOL {
/**
* 数据库连接字符串
*/
// private final String sqlconstr =
// "jdbc:sqlserver://127.0.0.1:1433;databaseName=XASW_TPH;user=sa;password=123456";//sa身份连接
private final String sqlconstr = "jdbc:sqlserver://127.0.0.1:1433;databaseName=XASW_TPH;user=noblegolf;password=noblegolf";// sa身份连接
Connection con = null;
PreparedStatement prestmt = null;
ResultSet rs = null;
/**
* 建立数据库连接 0成功 1失败
*/
public int Connect() {
try {
if (con != null && con.isClosed() == false)
return 0;
// Establish the connection.
System.out.println("begin connect.");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(sqlconstr);
System.out.println("connect success.");
return 0;
} catch (Exception e) {
e.printStackTrace();
return 1;
}
}
/**
* 查询 SQL语句执行方法
*
* @throws SQLException
* @sqlstr SQL语句
* @params 参数
**/
public ResultSet QuerySql(String sqlstr, Object... params) throws SQLException {
// Create and execute an SQL statement that returns some data.
prestmt = con.prepareStatement(sqlstr);
for (int i = 0; i < params.length; i++) {
prestmt.setObject(i + 1, params[i]);
}
rs = prestmt.executeQuery();
// Iterate through the data in the result set and display it
return rs;
}
/**
* 增删改 SQL语句执行方法
*
* @throws SQLException
* @sqlstr SQL语句
* @params 参数
**/
public int NonquerySql(String sqlstr, Object... params) throws SQLException {
prestmt = con.prepareStatement(sqlstr);
for (int i = 0; i < params.length; i++) {
prestmt.setObject(i + 1, params[i]);
}
return prestmt.executeUpdate();
}
/**
* 关闭数据库连接
*
* @throws SQLException
*/
public void Close() {
try {
if (rs != null) {
rs.close();
}
if (prestmt != null) {
prestmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
##4)UserDao.java
package com.luo.dal;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.luo.bean.CharacterRelation;
/**
* DAO(Data Access Object)层
*/
public class UserDao {
/**
* 插入一个汉字字符
* @param string
* @return
*/
public static int InsertCharacter(String character){
int Result = 0;
DBPool dbp = new DBPool();
if (dbp.Connect() == 1) {
System.out.println("数据库连接失败");
return 0;
}
String sql0 = "SELECT No FROM TB_Character WHERE Character = ?;";
try {
ResultSet rs = dbp.QuerySql(sql0, character);
int no = 0;
while (rs.next()) {
no = rs.getInt("no");
}
if(0 == no){
sql0 = "insert into TB_Character(Character)values(?)";
Result = dbp.NonquerySql(sql0, character);
}
return Result;
} catch (Exception e) {
e.printStackTrace();
return 0;
} finally {
// 关闭数据库连接
dbp.Close();
}
}
/**
* 查询一个汉字字符的key
* @param string
* @return
*/
public static int QueryCharacterKey(String s){
ResultSet Result = null;
int no = 0;
DBPool dbp = new DBPool();
if (dbp.Connect() == 1) {
System.out.println("数据库连接失败");
return 0;
}
String sql0 = "SELECT No FROM TB_Character where Character=?;";
try {
Result = dbp.QuerySql(sql0, s);
while (Result.next()) {
no = Result.getInt("No");
}
return no;
} catch (Exception e) {
e.printStackTrace();
return 0;
} finally {
// 关闭数据库连接
dbp.Close();
}
}
/**
* 插入一个汉字字符的关系
* @param string
* @return
*/
public static int InsertCharacterRalation(int lastNo, int nextNo){
ResultSet resultSet = null;
int result = 0;
DBPool dbp = new DBPool();
if (dbp.Connect() == 1) {
System.out.println("数据库连接失败");
return 0;
}
String sql0 = "SELECT No FROM TB_CharacterRelation WHERE LastNo = ? AND NextNo = ?;";
try {
resultSet = dbp.QuerySql(sql0, lastNo, nextNo);
int no = 0;
while (resultSet.next()) {
no = resultSet.getInt("No");
}
if( 0 != no){
sql0 = "UPDATE TB_CharacterRelation SET Weight = Weight + 1 WHERE No = ?;";
result = dbp.NonquerySql(sql0, no);
}else{
sql0 = "INSERT into TB_CharacterRelation( Weight, LastNo, NextNo) VALUES ( 1,?,?);";
result = dbp.NonquerySql(sql0, lastNo, nextNo);
}
return result;
} catch (Exception e) {
e.printStackTrace();
return 0;
} finally {
// 关闭数据库连接
dbp.Close();
}
}
/**
* CharacterRelation中查找到weight>1的nextNo和LastNo
* @param string
* @return
*/
public static List<CharacterRelation> QueryKeyWord(){
ResultSet Result = null;
DBPool dbp = new DBPool();
if (dbp.Connect() == 1) {
System.out.println("数据库连接失败");
return null;
}
/**
*
* 后期处理。当最小的weight都大于1时,则所有weight-最小值weight
*/
String sql0 = "SELECT * FROM TB_CharacterRalation where weight>2;";
try {
Result = dbp.QuerySql(sql0);
List<CharacterRelation> ListCharacterRelation = new ArrayList<CharacterRelation>();
CharacterRelation cr = new CharacterRelation();
while (Result.next()) {
cr.No = Result.getInt("No");
cr.LastNo = Result.getInt("LastNo");
cr.NextNo = Result.getInt("NextNo");
cr.Weight = Result.getInt("Weight");
ListCharacterRelation.add(cr);
}
return ListCharacterRelation;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
// 关闭数据库连接
dbp.Close();
}
}
}
##5)libs
下载地址:点击下载
#2,使用
##1)获取ResultSet中的值
String col1 = resultSet.getString("列名");