用户注册



邮箱：

密码：

用户登录


邮箱：

密码：
记住登录一个月忘记密码？

发表随想


还能输入：200字
云代码 - java代码库

JDBC与c3p0连接池

2017-05-21 作者： 逻辑与思维举报

[java]代码库

view sourceprint?
简述：
     1） JDBC简单实现数据库连接的流程步骤
     2） JDBC机制
     3） Statement&&PreparedStatement的优缺点
        3.1  SQL注入攻击与
        3.2  sql命令批处理
        3.3  自定义连接池的简单实现
        3.4  c3p0连接池的使用（涉及到文件流的读取和反射机制）
 
1.   jdbc实现mysql数据库连接
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
 
import org.junit.Test;
 
public class Demo03 {
    @Test
    public void insert(){
        //初始化
        Connection conn=null;
        Statement state=null;
        ResultSet rs=null;
        try {
            //2  获取数据库连接
            conn=JDBCUtil.getConn();
            //3 获取传输器
            state=conn.createStatement();
            // 4 发送sql语句
            String sql_insert="insert into account values(4,'ddd',1200)";
            int rows=state.executeUpdate(sql_insert);
            System.out.println("insert影响了"+rows+"行");
            String sql_sel="select * from account";
            rs=state.executeQuery(sql_sel);
            //5 打印输出查询结果
            while(rs.next()){
                //读取数据库表的列时，下标从1开始
                int id=rs.getInt(1);
                String name=rs.getString(2);
                double money=rs.getDouble(3);
                System.out.println("编号："+id+" "+"姓名："+name+" "+"薪资："+money);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtil.closeStream(conn,state, rs);
        }
    }
    @Test
    public void update(){
        //初始化
        Connection conn=null;
        Statement state=null;
        ResultSet rs=null;
        try {
            //2  获取数据库连接
            conn=JDBCUtil.getConn();
            //3 获取传输器
            state=conn.createStatement();
            // 4 发送sql语句
            String sql_update="update account set money=800 where id<3";
            int rows=state.executeUpdate(sql_update);
            System.out.println("insert影响了"+rows+"行");
            String sql_sel="select * from account";
            rs=state.executeQuery(sql_sel);
            //5 打印输出查询结果
            while(rs.next()){
                //读取数据库表的列时，下标从1开始
                int id=rs.getInt(1);
                String name=rs.getString(2);
                double money=rs.getDouble(3);
                System.out.println("编号："+id+" "+"姓名："+name+" "+"薪资："+money);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtil.closeStream(conn,state, rs);
        }
    }
    @Test
    public void delete(){
        // 初始化
        Connection conn=null;
        Statement state=null;
        ResultSet rs=null;
        try {
            //2 获取数据库连接
            conn=JDBCUtil.getConn();
            //3 获取传输器
            state=conn.createStatement();
            // 4 发送sql语句
            String sql_del="delete from account where id=4";
            int rows=state.executeUpdate(sql_del);
            System.out.println("delete操作影响了"+rows+"行");
            String sql="select * from account";
            rs=state.executeQuery(sql);
            // 5 打印输出
            while(rs.next()){
                int id = rs.getInt(1);
                String name=rs.getString(2);
                double money=rs.getDouble(3);
                System.out.println("编号："+id+" "+"姓名："+name+" "+"薪资："+money);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtil.closeStream(conn, state, rs);
        }
    }
}
    *********************************************************************************************************************
    *********************************************************************************************************************
    2. JDBCUtils工具类（实现配置文件的读取、资源释放等功能）
public class JDBCUtils {
    //将driver、url、user、password等信息写在一个配置文件里面，
    private static Properties prop=new Properties();
    private JDBCUtils(){}
    //静态块，加载config.properties文件
    static{
        //Properties prop=new Properties();
        try {
            //类加载器
            ClassLoader classLoader=JDBCUtils.class.getClassLoader();
            //获取配置文件的路径
            String path=classLoader.getResource("config.properties").getPath();
            prop.load(new FileInputStream(new File(path)));
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
         
    }
    public static Connection getConn(){
        try {
            //1 加载数据库驱动
            String driver=prop.getProperty("driver");
            Class.forName(driver);
            // 2 获取数据库连接
            String url=prop.getProperty("url");
            String user=prop.getProperty("user");
            String password=prop.getProperty("password");
            Connection conn=DriverManager.getConnection(url, user, password);
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    //关闭流操作
    public static void closeStream(Connection conn,Statement ps,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }finally{
                rs=null;
            }
        }
        if(ps!=null){
            try {
                ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }finally{
                ps=null;
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }finally{
                conn=null;
            }
        }
    }
}
    *********************************************************************************************************************
    *********************************************************************************************************************
    3.  Statement批处理
import java.sql.Connection;
import java.sql.Statement;
/**
 * 通过Statement对象实现批处理
 */
public class StatementBatch {
    public static void main(String[] args) {
        //初始化Connection  Statement对象
        Connection conn=null;
        Statement state=null;
         
        try {
            long start=System.currentTimeMillis();
            //获取数据库连接
            conn=JDBCUtils.getConn();
            //传输器
            state=conn.createStatement();
            //批量执行sql命令
            //state.addBatch("drop database if exists mydb4");
            //state.addBatch("create database mydb4");
            //state.addBatch("use mydb4");
            //state.addBatch("create table tb_batch(id int primary key auto_increment, name varchar(20))");
            state.addBatch("truncate table tb_batch");
            for(int i=0;i<100;i++){
                state.addBatch("insert into tb_batch values(null,'a')");
                state.addBatch("insert into tb_batch values(null,'b')");
                state.addBatch("insert into tb_batch values(null,'c')");
                state.addBatch("insert into tb_batch values(null,'d')");
                state.addBatch("insert into tb_batch values(null,'e')");
                state.addBatch("insert into tb_batch values(null,'f')");
            }
            //将批处理发送给数据库
            state.executeBatch();
            long end=System.currentTimeMillis();
            System.out.println("Statement批处理执行时间："+(end-start));
            System.out.println("执行完毕！");
             
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            JDBCUtils.closeStream(conn, state, null);
        }
    }
}
 
    *********************************************************************************************************************
    *********************************************************************************************************************
       4 . PreparedStatement实现sql批处理
          
import java.sql.Connection;
import java.sql.PreparedStatement;
 
public class PreparedStatementBatch {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            long start=System.currentTimeMillis();
            conn=JDBCUtils.getConn();
            //开启事务
            conn.setAutoCommit(false);
            //传输器,执行sql模板语句，
            String sql="insert into tb_batch values(null,?)";
            ps=conn.prepareStatement(sql);
            //执行sql
            ps.addBatch("use mydb4");
            ps.addBatch("truncate table tb_batch");
            for(int i=0;i<500;i++){
                ps.setString(1, "test"+i);
                ps.addBatch();
            }
            ps.executeBatch();
            //提交事务
            conn.commit();
            long end=System.currentTimeMillis();
            System.out.println("PreparedStatement批处理执行时间："+(end-start)+",执行完毕！！");
            System.out.println();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
}
    *********************************************************************************************************************
    *********************************************************************************************************************
 5. c3p0连接池
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
 
/**
 * 测试c3p0连接池
 */
public class C3P0Test {
    public static void main(String[] args) {
        //初始化变量
        Connection conn=null;
        Statement state=null;
        ResultSet rs=null;
        //ComboPooledDataSource此类有何作用
        ComboPooledDataSource pool = new ComboPooledDataSource();
        try {
            // 设置数据库连接信息
            /*
            * pool.setDriverClass("com.mysql.jdbc.Driver");
            * pool.setJdbcUrl("jdbc:mysql:///mydb1"); pool.setUser("root");
            * pool.setPassword("root");
            */
 
            // 从连接池中获取一个连接
            conn=pool.getConnection();
            state = conn.createStatement();
            rs = state.executeQuery("select * from account where id=1");
            if (rs.next()) {
                System.out.println(rs.getString("name") + ":"
                        + rs.getDouble("money"));
            }
 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeStream(null, state, rs);
            if(conn!=null){
                try {
                    conn.close();//还回连接。不是关闭
                } catch (Exception e) {
                    e.printStackTrace();
                                        throw new RuntimeException();
                }finally{
                    conn=null;
                }
            }
        }
    }
}
 *********************************************************************************************************************
 *********************************************************************************************************************
6.两种c3p0配置文件格式：
   
 c3p0.xml
 
    <?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <!-- setting -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///mydb1</property>
        <property name="user">root</property>
        <property name="password">root</property>
    </default-config>
 
    <named-config name="config1">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///mydb1</property>
        <property name="user">admin</property>
        <property name="password">123</property>
    </named-config>
</c3p0-config>
 
    c3p0.properties
 
        c3p0.driverClass=com.mysql.jdbc.Driver
        c3p0.jdbcUrl=jdbc:mysql:///mydb1
        c3p0.user=root
        c3p0.password=root
 
             

[源代码打包下载]




网友评论    (发表评论)

共6 条评论 1/1页

发表评论：

评论须知：

  • 1、评论每次加2分，每天上限为30；
  • 2、请文明用语，共同创建干净的技术交流环境；
  • 3、若被发现提交非法信息，评论将会被删除，并且给予扣分处理，严重者给予封号处理；
  • 4、请勿发布广告信息或其他无关评论，否则将会删除评论并扣分，严重者给予封号处理。


扫码下载

加载中，请稍后...

输入口令后可复制整站源码

加载中，请稍后...