JDBC、spring的JdbcTemplate、apache的DBUtils的介绍与使用

JDBC使用步骤

1. 导入JDBC驱动jar
2. 注册JDBC驱动
   参数：“驱动程序类名”
   Class.forName("驱动程序类名“）
3. 获得Connection对象
   需要三个参数：url, username,password连接到数据库
4. 创建Statement（语句）对象
   conn.getStatement()方法创建对象，用于执行SQL语句。
   （1）execute（ddl)执行任何SQL，常用执行DDL
   （2）executeUpdate(dml)执行DML语句，如：insert、update、delete等
   （3）executeQuery(dml)执行DQL语句，如：select
5. 处理SQL执行结果
   execute(ddl)如果没有异常则成功
   executeUpdate（dml)返回数字表示更新“行”数量，抛出异常则失败
   executeQuery（dml)返回ResultSet(结果集）对象，代表二维查询结果，使用for遍历处理，如果查询失败抛出异常！
6. 关闭数据连接！
   conn.close();
   实现代码：

package cn.xiaoren.jdbc.day01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.Statement;

public class Demo02 {
  public static void main(String args[]) throws Exception{
	
		 
	 
	  //注册驱动
		  Class.forName("com.mysql.jdbc.Driver");
		  //连接到数据库
		  String url="jdbc:mysql://localhost:3306/mysql?characterEncoding=utf8&useSSL=false";
		  String username="root";
		  String passward="12345678";
		  Connection conn =DriverManager.getConnection(url,username,passward);
		  //创建Statement
		  Statement st=  (Statement) conn.createStatement();
		  //执行DML,使用executeUpdate方法
//		  String dml="insert into Student"+"(sno,sname)"+"values(1,'Tom')";
		String dml="update Student "+"set sname='jerry'  "+"where sno=1"; 
//         String dml="delete from Student "+"where sno=1";		  
		  int n=st.executeUpdate(dml);
		  System.out.println(n);
		  //关闭连接
		  conn.close();
				  
	  
	  
  }
}

切记SQL语句与连接符之间应有空格。

Spring JdbcTemplate使用

有篇文写的太好了，感觉我创作的肯定不可及，推荐给大家，作者：浅然言而言
博客连接：
https://blog.csdn.net/w_linux/article/details/80285491

apache的DBUtils的使用

1. 导入jar包
   c3p0-0.9.1.2.jar
   commons-dbutils-1.4.jar
   commons-logging-1.1.1.jar
   mysql-connector-java-5.1.49.jar

2.导入配置文件

配置文件： c3p0-config.xml。

<c3p0-config>
    <!--使用默认的配置获取连接池对象-->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db1</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <!--连接池参数-->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">10</property>
        <property name="checkoutTimeout">2000</property>
        <property name="maxIdleTime">1000</property>

    </default-config>
    <named-config name="mysqlc3p0">
        <!--连接参数-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db3</property>
        <property name="user">root</property>
        <property name="password">yanwenren</property>
        <!--连接池参数-->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">15</property>
        <property name="checkoutTimeout">2000</property>
        <property name="maxIdleTime">1000</property>
    </named-config>


</c3p0-config>

3. 对数据库db1中的表格Account进行增、删、改、查操作

Account表格：
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | 小红 |       0 |
|  2 | 张三 |    2000 |
|  3 | 王五 |    1000 |
+----+------+---------+

增加数据的代码：

package com.offcn.pooledDatasource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.Connection;
import java.sql.SQLException;

public class Demo3 {
    public static void main(String[] args) throws SQLException {
        ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource("mysqlc3p0");

        Connection connection=comboPooledDataSource.getConnection();

        QueryRunner queryRunner=new QueryRunner();
        String sql="insert into account(id,name,balance)values(?,?,?)";
        int update=queryRunner.update(connection,sql,7,"小明",3000);
        System.out.println(update);
        connection.close();


    }
}

删除数据的代码：

package com.offcn.pooledDatasource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.Connection;
import java.sql.SQLException;

public class Demo5 {
    public static void main(String[] args) throws SQLException {
        ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource();
        Connection connection=comboPooledDataSource.getConnection();
        QueryRunner queryRunner=new QueryRunner();
        String sql="delete from account where id=?";
        queryRunner.update(connection,sql,4);
        connection.close();

    }
}

更改数据的代码：

package com.offcn.pooledDatasource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.Connection;
import java.sql.SQLException;

public class Demo4 {
    public static void main(String[] args) throws SQLException {
        ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource();
        Connection connection=comboPooledDataSource.getConnection();
        QueryRunner queryRunner=new QueryRunner();
        String sql="update account set name='小红'where id=?";
        queryRunner.update(connection,sql,"1");
        connection.close();

    }
}

查询数据的代码：

package com.offcn.pooledDatasource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.SQLException;

public class Demo6 {
    public static void main(String[] args) {


        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "select * from account where id=?";
        Account account;

        {
            try {
                account = queryRunner.query(sql, new BeanHandler<>(Account.class),1);
                System.out.println(account);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}