2. 将数据库连接封装到一个类中,利用配置文件连接,静态返回connection
- url = jdbc:mysql://localhost:3306/test
- user = root
- password = admin
- package study.jdbc;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.IOException;
- import java.io.InputStream;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.SQLException;
- import java.util.Properties;
- public class DBConnect {
- static String url;
- static String user;
- static String password;
- /**
- * 获取一个JDBC连接,返回一个Connection对象
- * @return connection
- */
- public static Connection connectDB() {
- Connection connection = null;
- readProperties();
- try {
- Class.forName("com.mysql.jdbc.Driver");
- connection = DriverManager.getConnection(url, user, password);
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- }
- return connection;
- }
- /**
- * 读取properties文件,获取url,user,password
- */
- private static void readProperties() {
- String fileName = "resouce/db_connect.properties"; //相对于工程
- Properties properties = new Properties();
- try {
- InputStream in = new FileInputStream(fileName);
- properties.load(in);
- in.close();
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- url = properties.getProperty("url");
- user = properties.getProperty("user");
- password = properties.getProperty("password");
- }
- }
- package study.jdbc;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- public class TestMain {
- public static void main(String[] args) {
- Connection connection = DBConnect.connectDB(); //获取数据库连接
- TestMain test = new TestMain();
- try { //测试
- test.clear(connection);
- test.insert(connection); //增
- test.query(connection);
- System.out.println("----------");
- test.delete(connection); //删
- test.query(connection);
- System.out.println("----------");
- test.update(connection); //改
- test.query(connection); //查
- connection.close(); //关闭数据库连接
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- /**
- * 使用PreparedStatement,效率高
- * 动态执行SQL(带参数的SQL语句),是Statement子接口
- * 对数据库进行insert,用带参数的语句批量插入
- * @param connection
- * @throws SQLException
- */
- public void insert(Connection connection) throws SQLException {
- String sql = "INSERT INTO mytable(id,name,age) values (?,?,22);";
- PreparedStatement pr = connection.prepareStatement(sql);
- for (int i = 1; i <= 3; i++) {
- pr.setInt(1, i);
- pr.setString(2, "demo"+i);
- pr.executeUpdate();
- }
- }
- /**
- * 对数据库进行delete
- * @param connection
- * @throws SQLException
- */
- public void delete(Connection connection) throws SQLException {
- String sql = "delete from mytable where id=2;";
- PreparedStatement pr = connection.prepareStatement(sql);
- pr.executeUpdate();
- }
- /**
- * 对数据库进行update
- * @param connection
- * @throws SQLException
- */
- public void update(Connection connection) throws SQLException {
- String sql = "UPDATE mytable SET name='new' WHERE id=1;";
- PreparedStatement pr = connection.prepareStatement(sql); // 创建statement对象发送SQL到数据库
- pr.executeUpdate(); // 执行UPDATE
- }
- /**
- * 查询query遍历结果集
- * @param connection
- * @throws SQLException
- */
- public void query(Connection connection) throws SQLException {
- String sql = "select * from mytable;";
- PreparedStatement st = connection.prepareStatement(sql);
- ResultSet re = st.executeQuery(); // 查询,返回单个ResultSet对象
- while (re.next()) {
- int id = re.getInt(1);
- String name = re.getString(2);
- int age = re.getInt(3);
- System.out.println(id + "\t" + name + "\t" + age);
- }// 遍历结果集
- }
- /**
- * 清空表
- * @param connection
- * @throws SQLException
- */
- public void clear(Connection connection) throws SQLException {
- String sql = "delete from mytable;";
- PreparedStatement pr = connection.prepareStatement(sql);
- pr.executeUpdate();
- }
- }
来源: http://www.phpxs.com/code/1002166/