Java JDBC 小例子

来源：互联网发布：php 环境编辑：程序博客网时间：2024/06/07 17:02

一直以来项目都是用Hibernate，JDBC反而没有正经练习过，现在将JDBC的基本使用做个小练习。

1. 使用MySQL建立一个test数据库，里面建立一个mytable表，3列（id，name，age）；将url, user, password配制成properties文件，放到工程resource源代码包下面，这里将其命名为db_connect.properties文件

url = jdbc:mysql://localhost:3306/testuser = rootpassword = admin

2. 将数据库连接封装到一个类中，利用配置文件连接，静态返回connection

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");}}

3. 对数据库进行增删改查的测试，主要练习使用PreparedStatement

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();}}

0 0