【JDBC】数据库连接（含c3p0连接池）

数据库连接

直连方式

配置文件jdbc.properties，手动加载

driverName=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://127.0.0.1:3306/fei
user=root
password=密码

InputStream is = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String driverName = properties.getProperty("driverName");
String jdbcUrl = properties.getProperty("jdbcUrl");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
Class.forName(driverName);
java.sql.Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

DBCP连接池

配置文件dbcp.properties，手动加载

username=root
password=密码
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/fei

initialSize=10
maxActive=20
minIdle=5
maxWait=5000

Properties properties = new Properties();
InputStream is = getClass().getClassLoader().getResourceAsStream("dbcp.properties");
properties.load(is);

DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
BasicDataSource basicDataSource=(BasicDataSource) dataSource;//basicDataSource.getXxx() .setXxx(value)  其中Xxx为参数名
Connection connection = dataSource.getConnection();

c3p0连接池（推荐

配置文件c3p0-config.xml，自动加载

<c3p0-config>
    <default-config>
        //默认配置
        <property name="driverClass">com.mysql.jdbc.Driver</property>  
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/fei</property>
        <property name="user">root</property>
        <property name="password">密码</property>
        <property name="initialPoolSize">5</property>
        <property name="minPoolSize">3</property>
        <property name="maxPoolSize">20</property>
        <property name="acquireIncrement">5</property>
    </default-config>
    <named-config name="demo">
        ...//非默认配置，通过name指定的字符串获取
    </named-config>
</c3p0-config>

ComboPooledDataSource dataSource = new ComboPooledDataSource(); //空参加载c3p0-config.xml的默认配置   dataSource.getXxx()
Connection connection = dataSource.getConnection();

数据库操作

普通方式

//1.获取connection连接

//2.创建PrepareStatement对象
String sql = "Insert into personinfo(name,email,picture) values(?,?,?);";
PreparedStatement pst = connection.prepareStatement(sql);

//3.填充占位符
pst.setString(1,"飛");  //索引从1开始
pst.setString(2,"wefdd@163.com");
FileInputStream is = new FileInputStream(new File("src1.jpg")); //先读进内存，再插入数据库
pst.setBlob(3,is); //Blob类型存储大对象

//4.执行增删改
pst.executeUpdate(); 

//4.执行查询
ResultSet rs = pst.executeQuery(); //获得resultSet结果集
FileOutputStream os = new FileOutputStream(new File("1.jpg")); 
while (rs.next()) { //while循环处理resultSet
    int id = rs.getInt(1);
    String name = rs.getString(2);
    String email = rs.getString(3);
    Blob blob = rs.getBlob(4); #大对象获取   
    InputStream bs = blob.getBinaryStream(); //读进内存，然后再保存本地
    byte[] cbuf = new byte[10];
    int len;
    while ((len = bs.read(cbuf))!=-1) {
         os.write(cbuf, 0, len);
    }
}

//5.关闭打开的各种资源connection、pst、rs等

案例：查询SQL返回对象列表：

public static <T> List<T> getSQLInstanceList(Class<T> clazz,String sql,Object... args) {
    List<T> instanceList = new ArrayList<>();
    T instance = null;  
    connection = getConnection();//1.链接数据库
    pst = connection.prepareStatement(sql); //2实例化prepareStatement
    for (int i = 0; i <args.length; i++) { //3.填充sql语句的占位符
        pst.setObject(i+1,args[i]);
    }
    rs = pst.executeQuery();//4.获得resultSet结果集
    ResultSetMetaData metaData = rs.getMetaData(); //5.通过结果集获取MetaData
    while (rs.next()) { //6.while循环处理resultSet
        instance = clazz.newInstance();
        for (int i = 0; i <metaData.getColumnCount() ; i++) {
            String columnLabel = metaData.getColumnLabel(i + 1); //6.1解析列名
            Object columnValue = rs.getObject(i + 1); //解析值
            BeanUtils.setProperty(instance, columnLabel, columnValue);  //6.2属性赋值
        }
        instanceList.add(instance);
   return instanceList;

DBUtils（推荐

QueryRunner queryRunner = new QueryRunner();
//增删改
int update= queryRunner.update(connection,sql, "飛", "wefdd@163.com", is); //sql后面的参数是填充占位符的，其中is是图片输入流

#查：就以查询结果以对象列表存储
List personInfoList = queryRunner.query(connection, sql, new BeanListHandler(PersonInfo.class)); //返回值存储形式通过第三个参数设置