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

来源：互联网发布：东北师范大学网络教育编辑：程序博客网时间：2024/06/14 17:51

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

阅读全文

0 0