JDBC&DBCP连接Mysql的实现思路及样例

无论是使用JDBC还是DBCP连接池都需要相关jar包依赖，先将jar包到入到项目路径下才能成功获得mysql连接
导jar包，本人使用的是eclipse

       jdbc依赖的jar包  

       mysql-connector-java-5.1.32.jar 导到项目目录下的lib文件，右键项目名 Build Path -->Configure Build Path --> libraries -- > add jar

      DBCP依赖的jar包

      commons-dbcp2-2.2.0.jar    commons-dbcp2-2.2.0.jar  commons-pool2-2.5.0.jar  与jdbc jar包导入相同

注册驱动

String className  com. mysql.jdbc. Driver

class.forName(className);

建立连接

       String  url,user,password

    Connection conn=DviverManager.getConnection(url,user,password);

SQL语句编写及预编译    PreparedStatement是安全的实现,无sql注入

StringBuffer  sql

PreparedStatement stmt = conn.prepareStatement(sql.toString());

SQL语句执行 选一查询或更新

ResultSet rs = stmt.executeQuery();

stmt.executeUpda();

对查询的读取

    while (rs.next()) {

                     int id = rs.getInt("id");

                     String name = rs.getString("name");

                     double salary = rs.getDouble("salary");

                     System.out.println("编号" + id + "，姓名" + name + "，余额" + salary);

              }

关闭连接,释放资源

rs.close();stmt.close();conn.close();

 

改进思路

重复代码封装成工具类，JDBCUtil

使用连接池的方式获取连接

使用配置文件的方式读取用户名密码  键值对，io流

properties文件作为配置文件，其内容为，需注意若没改过properties文件的编码格式，请不要插入中文，或者读文件时注意编码与原文件相同

#this is a configuration file(it is a text file actually)
#\u8005\u662FJDBC\u8FDE\u63A5\u6C60\u7684\u914D\u7F6E
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test01
user=root
pwd=root
 

代码实现

普通实现 

   public static void test7() {

              Connection conn = null;

              PreparedStatement stmt = null;

              ResultSet rs = null;

              try {

                     String className = "com.mysql.jdbc.Driver";

                     Class.forName(className);



                     String url = "jdbc:mysql://127.0.0.1:3306/test01";

                     String user = "root";

                     String password = "root";

                     conn = DriverManager.getConnection(url, user, password);

                     StringBuffer sql = new StringBuffer();

                     // 10.查询Score表中的最高分的学生学号和课程号。（子查询或者排序）

                     sql.append("select sno,cno,degree  ");

                     sql.append("from score sc where degree in ( ");

                     sql.append("  select max(degree)  ");

                     sql.append("  from score ");

                     sql.append("  where cno = sc.cno  ");

                     sql.append("  group by cno); ");



                     stmt = conn.prepareStatement(sql.toString());

                     rs = stmt.executeQuery();// stmt.excuteUpdate();

                     while (rs.next()) {

                            int sno = rs.getInt("sno");

                            String cno = rs.getString("cno");

                            int degree = rs.getInt("degree");

                            System.out.println(sno + "\t" + cno + "\t" + degree);

                     }

              } catch (Exception e) {

                     e.printStackTrace();

              } finally {

                     CloseUtil.close(rs);

                     CloseUtil.close(stmt);

                     CloseUtil.close(conn);

              }

       }

工具类的实现：

import java.sql.Connection;

import java.sql.DriverManager;

public class JDBCUtil {

       public static Connection getConnection() throws Exception {

              Connection conn = null;

              String className = "com.mysql.jdbc.Driver";

              Class.forName(className);



              String url = "jdbc:mysql://127.0.0.1:3306/customdb";//10.0.0.1:9300/my_db

              String user="root";

              String password = "root";

             

              conn = DriverManager.getConnection(url, user, password);

              return conn;

       }

}

配置文件 结合工具类实现

package utiltest;



import java.io.File;

import java.io.FileInputStream;

import java.io.InputStreamReader;

import java.sql.Connection;

import java.sql.DriverManager;

import java.util.Properties;



public class JDBCConfUtil {

       public static Connection getConnection() throws Exception {

              Connection conn = null;

             

              Properties p = new Properties();

              String file = "src" + File.separator + "exc" + File.separator + "conf.properties";

              p.load(new InputStreamReader(new FileInputStream(file), "utf-8"));

              String driver = p.getProperty("driver");

              String url = p.getProperty("url");

              String user = p.getProperty("user");

              String pwd = p.getProperty("pwd");

             

              Class.forName(driver);

             

              conn = DriverManager.getConnection(url, user, pwd);

              return conn;

       }

}

连接池 结合配置文件的实现                

package exc;



import java.io.File;

import java.io.FileInputStream;

import java.io.InputStreamReader;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.Properties;



import org.apache.commons.dbcp2.BasicDataSource;



import util.CloseUtil;



public class DBCPgetConnectionUtil {

                      public static void test() {

                            Connection conn = null;

                            PreparedStatement stmt = null;

                            ResultSet rs = null;

                            BasicDataSource bds = null;

                            try {

                                   Properties p = new Properties();

                                   String file = "src" + File.separator + "exc" + File.separator

                                                 + "conf.properties";

                                   p.load(new InputStreamReader(new FileInputStream(file), "utf-8"));

                                   String driver = p.getProperty("driver");

                                   String url = p.getProperty("url");

                                   String user = p.getProperty("user");

                                   String pwd = p.getProperty("pwd");



                                   bds = new BasicDataSource();

                                   bds.setDriverClassName(driver);

                                   bds.setUrl(url);

                                   bds.setUsername(user);

                                   bds.setPassword(pwd);

                                   conn = bds.getConnection();



                                   StringBuffer sql = new StringBuffer();

                                   sql.append("select sno,sname,ssex from student ");

                                   sql.append("where ssex in ('男'); ");

                                   stmt = conn.prepareStatement(sql.toString());



                                   rs = stmt.executeQuery();

                                   while (rs.next()) {

                                          String sno = rs.getString("sno");

                                          String sname = rs.getString("sname");

                                          String ssex = rs.getString("ssex");

                                          System.out.println("sno:" + sno + "  sname:" + sname

                                                        + "  ssex:" + ssex);

                                   }

                            } catch (Exception e) {

                                   e.printStackTrace();

                            } finally {

                                   CloseUtil.close(rs);

                                   CloseUtil.close(stmt);

                                   CloseUtil.close(conn);

                                   CloseUtil.close(bds);

                            }



                      }



                      public static void main(String[] args) throws Exception {

                            test();

                      }

}