Javaweb——简单的JDBC连接的几种书写方式

我们都知道JDBC是用来对数据库进行连接的，下面让我们对数据库进行的连接方式来进行介绍；

package com.test;

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


public class DBUtil {
	public static Connection getConn() {//声明一个方法，用来连接
		Connection conn=null;
		try {
			//加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获取连接
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
//	声明一个方法用来释放资源
	public static void release(ResultSet rs,PreparedStatement ps,Connection conn) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

下面让我们来建立一个测试类来测试一下我们上面书写的两个方法；

package com.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

public class DBUTtest {
	@Test
	public void test() {
		Connection conn = null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		//1.获取连接
		conn = DBUtil.getConn();
		//2.编写SQL
		String sql = "select * from user where name =?";
		//3获取SQL对象
		try {
			ps = conn.prepareStatement(sql);
			//4.设置参数
			ps.setString(1, "lq");
			//5.执行查询 
			rs = ps.executeQuery();
			//6.处理结果集
			while(rs.next()) {
				String name = rs.getString("pwd");
				System.out.println(name);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//7.释放资源
			DBUtil.release(rs, ps, conn);
		}
	}
}

 一般的比较常规的写法就是上面这种，接下来我们可以用一种配置文件的方式进行书写，我们首先通过new来建立一个配置文件命名为db.properties,在这个配置信息中我们需要进行如下信息的书写：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=

接下来就是我们要写的连接方法以及释放资源的方法，如下：

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

public class DBUtil02 {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	static {
		ResourceBundle rb = ResourceBundle.getBundle("db");
		driver = rb.getString("driver");
		url = rb.getString("url");
		username = rb.getString("username");
		password = rb.getString("password");
	}
	public static Connection getCon() {
		Connection conn= null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
//	声明一个方法用来释放资源
	public static void release(ResultSet rs,PreparedStatement ps,Connection conn) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
}

接下来我们进行测试，我们在user表中添加一列数据，具体实现如下：

package com.test;

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

import org.junit.Test;

public class DBUtil02Test {
	@Test
	public void Test02() {
		Connection conn =null;
		PreparedStatement ps = null;
		//1.连接
		conn = DBUtil02.getCon();
		//2.书写SQL
		String sql = "insert into user(name,pwd) values (?,?)";
		//3.获取SQL对象
		try {
			ps = conn.prepareStatement(sql);
			//4.为占位符设置值
			ps.setString(1, "lmy");
			ps.setInt(2, 123456);
			//5.执行SQL
			int rs = ps.executeUpdate();
			if(rs>0) {
				System.out.println("插入成功！");
			}else {
				System.out.println("插入失败！");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil02.release(null, ps, conn);
		}
		
	}
}

在上面的配置文件的过程中还有一种方式是先获取到 当前的类加载器，在获取一个输入流，通过load方法把这个字节流load进去，具体实现如下：

package com.test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ResourceBundle;

import org.junit.Test;

public class DBUtil03 {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	static {
		//获取一个类加载器
		ClassLoader classLoader = DBUtil03.class.getClassLoader();
		//根据类加载器获取一个输入流
		InputStream resourceAsStream = classLoader.getResourceAsStream("db.properties");
		//创建一个properties对象
		Properties properties = new Properties();
		try {
			//加载输入流
			properties.load(resourceAsStream);
			//获取相关的值
			driver = properties.getProperty("driver");
			url = properties.getProperty("url");
			username = properties.getProperty("username");
			password = properties.getProperty("password");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static Connection getCon() {
		Connection conn= null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	@Test
	public void test() {
		Connection conn = null;
		PreparedStatement ps = null;
		// 1.连接
		conn = DBUtil03.getCon();
		// 2.书写SQL
		String sql = "delete from user where name =?";
		// 3.获取SQL对象
		try {
			ps = conn.prepareStatement(sql);
			// 4.为占位符设置值
			ps.setString(1, "lq");
			// 5.执行SQL
			int rs = ps.executeUpdate();
			if (rs > 0) {
				System.out.println("删除成功！");
			} else {
				System.out.println("删除失败！");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil02.release(null, ps, conn);
		}

	}
}