无框架环境下JDBC连接的实现
1.手写JDBC直连
1.1基本功能实现的尝试
思路：
1.建立InputStream in，从配置文件读取url、user、password、driver等
2.用properties接收上述读取信息（ps.load(in)），存储在变量中
3.获取链接实例（getConnection（）方法）
4.用完记得close in流
package com.my.connection;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
class test{
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/girls?serverTimezone=UTC&useSSL=false";
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "123456");
Connection conn = DriverManager.getConnection(url,info);
System.out.println(conn);
}
}
一般我们会将参数保存在.properties(当然还有.yaml)中，这里尝试使用java原生IO流读取properties中的信息并根据信息完成连接。
package com.my.connection;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
class test{
public static void main(String[] args) throws Exception {
InputStream is = test.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(is);
String url=pros.getProperty("url");
String user=pros.getProperty("user");
String password=pros.getProperty("password");
String driver=pros.getProperty("driver");
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
}
下面是完整的链接-增删改业务实现
package com.preparedstatement.crud;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
public class test {
public static void main(String[] args) {
ConnectionManager CM=new ConnectionManager("src/jdbc.properties");
String sql= "INSERT INTO`beauty`\n" +
"SET`name`='余致庆',`phone`='110',`boyfriend_id`='110';\n";
CM.CommandHandler(sql);
}
}
class ConnectionManager{
private String url=null;
private String driver=null;
private String user=null;
private String password=null;
private String dir;
private PreparedStatement ps = null;
public ConnectionManager(String location){
dir=location;
}
private InputStream in= null;
private void InfoReader(){
Properties pros=new Properties();
try {
in = new FileInputStream(dir);
pros.load(in);
} catch (Exception e) {
e.printStackTrace();
}
user= pros.getProperty("user");
password= pros.getProperty("password");
url= pros.getProperty("url");
driver= pros.getProperty("driver");
}
private void DriverInit(){
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private Connection getNewConnection(){
Connection conn= null;
try {
conn = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private Connection Init(){
InfoReader();
DriverInit();
Connection con=getNewConnection();
return con;
}
void CommandHandler(String sql){
Connection conn=Init();
try {
ps=conn.prepareStatement(sql);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
in.close();
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
PreparedStatement getPS(){
return ps;
}
}
如果加入查询：
package com.preparedstatement.crud;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class test {
public static void main(String[] args) {
ConnectionManager CM=new ConnectionManager("src/jdbc.properties");
String sql= "SELECT*FROM `beauty`WHERE`name`like'An%';";
CM.QueryHandler(sql);
}
}
class ConnectionManager{
private String url=null;
private String driver=null;
private String user=null;
private String password=null;
private String dir;
private PreparedStatement ps = null;
public ConnectionManager(String location){
dir=location;
}
private InputStream in= null;
private void InfoReader(){
Properties pros=new Properties();
try {
in = new FileInputStream(dir);
pros.load(in);
} catch (Exception e) {
e.printStackTrace();
}
user= pros.getProperty("user");
password= pros.getProperty("password");
url= pros.getProperty("url");
driver= pros.getProperty("driver");
}
private void DriverInit(){
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private Connection getNewConnection(){
Connection conn= null;
try {
conn = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private Connection Init(){
InfoReader();
DriverInit();
Connection con=getNewConnection();
return con;
}
void CommandHandler(String sql){
Connection conn=Init();
try {
ps=conn.prepareStatement(sql);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
in.close();
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
PreparedStatement getPS(){
return ps;
}
beauty[] QueryHandler(String sql){
Connection conn=Init();
ResultSet rs = null;
beauty[] b=new beauty[22];
try {
ps=conn.prepareStatement(sql);
rs= ps.executeQuery();
int count=0;
while(1==1){
if(rs.next()){
b[count]=new beauty(rs.getString("name"),
rs.getString("sex"),
rs.getInt("boyfriend_id"));
count++;
}
else {
System.out.println("共检索到"+count+"个对象");
break;
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
in.close();
ps.close();
rs.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return b;
}
}
对象类：
class beauty{
private String name;
private String sex;
private int boyfriend_id;
public beauty(){
}
public beauty(String str1,String str2,int num1){
name=str1;
sex=str2;
boyfriend_id=num1;
}
}
1.2正式完成业务的通法
建表：
CREATE TABLE IF NOT EXISTS customers(
cust_id INT PRIMARY KEY,
cust_name VARCHAR(20) UNIQUE,
email VARCHAR(20),
birth DATE,
photo BLOB
);
INSERT INTO customers(`cust_id`,`cust_name`,`email`,`birth`)
VALUES(1,'汪峰','wf@126.com','1990-12-12');
INSERT INTO customers(`cust_id`,`cust_name`,`email`,`birth`)
VALUES(3,'阿庆','yzq@126.com','2000-10-01');
主程序
package com.preparedstatement.crud;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.Properties;
import static java.lang.Class.forName;
public class test {
public static void main(String[] args) throws SQLException {
customer c1=new customer();
String sql="SELECT`cust_id`,`cust_name`,`email`,`birth`FROM `customers`WHERE`cust_id`<=3;";
String dir="jdbc.properties";
Info info=new Info();
info.readInfo(dir);
customer[] c=ConnectionManager.Query(info,sql);
System.out.println(c[0].cust_id);
}
}
class customer{
int cust_id;
String cust_name;
String email;
Date birth;
public customer() {
}
}
class Info{
String url;
String driver;
String user;
String password;
void readInfo(String dir) {
InputStream in= null;
in = ClassLoader.getSystemResourceAsStream(dir);
Properties pros=new Properties();
try {
pros.load(in);
} catch (IOException e) {
e.printStackTrace();
}
url= pros.getProperty("url");
driver= pros.getProperty("driver");
user=pros.getProperty("user");
password= pros.getProperty("password");
try {
forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
class ConnectionManager{
static customer[] Query(Info info,String sql) throws SQLException {
Connection conn=DriverManager.getConnection(info.url,info.user,info.password);
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs=ps.executeQuery();
customer[] c=new customer[22];
customer t=new customer();
int count=rs.getMetaData().getColumnCount();
Field field;
int j=0;
while(rs.next()){
for(int i=1;i<=count;i++){
String label=rs.getMetaData().getColumnLabel(i);
try {
field=customer.class.getDeclaredField(label);
field.setAccessible(true);
field.set(t,rs.getObject(i));
} catch (NoSuchFieldException | IllegalAccessException e) {
e.printStackTrace();
}
if(i==count)c[j]=t;
}
j++;
}
if(j!=-1)System.out.println("共搜寻到"+(j)+"个结果");
return c;
}
}
加入并发和泛型，可以使程序泛用性和稳定性得到进一步优化
/加入泛型，进一步优化
package com.preparedstatement.crud;
import java.io.*;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;
import static java.lang.Class.forName;
class test{
public static void main(String[] args) throws Exception {
Info info=new Info();
Class clazz=customer.class;
customer[] c=new customer[22];
info.readInfo("jdbc.properties");
try {
c= (customer[]) ConnectionManager.Query(info,"SELECT`cust_id`,`cust_name`,`email`,`birth`FROM customers;",clazz);
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println(c[0].cust_name+""+c[1].cust_name+c[2].cust_name);
}
}
class customer{
int cust_id;
String cust_name;
String email;
Date birth;
public customer(){
}
}
class Info{
String url;
String driver;
String user;
String password;
void readInfo(String dir) {
InputStream in= null;
in = ClassLoader.getSystemResourceAsStream(dir);
Properties pros=new Properties();
try {
pros.load(in);
} catch (IOException e) {
e.printStackTrace();
}
url= pros.getProperty("url");
driver= pros.getProperty("driver");
user=pros.getProperty("user");
password= pros.getProperty("password");
try {
forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
class ConnectionManager<T>{
static<T> T[] Query(Info info,String sql,Class<T> clazz) throws Exception {
Connection conn=DriverManager.getConnection(info.url,info.user,info.password);
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs=ps.executeQuery();
T[] c= (T[]) Array.newInstance(clazz,22);
int count=rs.getMetaData().getColumnCount();
Field field;
int j=0;
while(rs.next()){
T t = clazz.getConstructor().newInstance();
for(int i=1;i<=count;i++){
String label=rs.getMetaData().getColumnLabel(i);
try {
field=t.getClass().getDeclaredField(label);
field.setAccessible(true);
field.set(t,rs.getObject(label));
} catch (NoSuchFieldException | IllegalAccessException e) {
e.printStackTrace();
}
finally {
if(i==count){c[j]=t;j++;}
}
}
}
if(j!=-1)System.out.println("共搜寻到"+(j)+"个结果");
return c;
}
static void search(String sql){
}
}
static void command(Info info,String sql){
Connection conn= null;
PreparedStatement ps=null;
try {
conn = DriverManager.getConnection(info.url,info.user,info.password);
conn.setAutoCommit(false);
ps = conn.prepareStatement("UPDATE `customers`SET `BALANCE`=`BALANCE`+100 WHERE`cust_id`=3;");
ps.execute();
System.out.println(10/0);
ps = conn.prepareStatement("UPDATE `customers`SET `BALANCE`=`BALANCE`-100 WHERE`cust_id`=1;");
ps.execute();
ps = conn.prepareStatement("commit;");
ps.execute();
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally{
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.引入C3P0
首先是C3P0的配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<named-config name="Test1130">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">llx260032</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/girls?serverTimezone=UTC&useSSL=false</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">10</property>
<property name="maxPoolSize">50</property>
<property name="maxStatements">50</property>
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
测试的主方法：
package com.preparedstatement.crud;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class test {
public static void main(String[] args) {
ComboPooledDataSource cpds = new ComboPooledDataSource("Test1130");
Connection conn = null;
try {
conn = cpds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println(conn);
}
}
增删改查业务和方法类写法与直连并无不同，在此不再赘述
3.引入Druid连接
class JDBCManager{
static DataSource ds;
static {
InputStream in = null;
Properties pros =new Properties();
try {
in = new FileInputStream("C:\\Users\\Administrator\\IdeaProjects\\untitled7/src/jdbc.properties");
pros.load(in);
ds = DruidDataSourceFactory.createDataSource(pros);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
static void command(String sql) throws SQLException {
Connection conn= ds.getConnection();
PreparedStatement ps=conn.prepareStatement(sql);
ps.execute();
ps.close();
conn.close();
}
}
增删改查业务和方法类写法与直连并无不同，在此不再赘述
|