使用MySQL建立一个test数据库，里面建立一个mytable表，3列（id，name，age）；将url, user, password配制成properties文件，放到 工程resource源代码包下面，这里将其命名为db_connect.properties文件 url = jdbc:mysql://localhost:3306/test user = root password = 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(); } }
