【java学习】数据库的连接与使用

#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("列名");