Java JDBC 小例子，javajdbc例子,1.使用MySQL建立一

Java JDBC 小例子，javajdbc例子,1.使用MySQL建立一

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

2.将数据库连接封装到一个类中，利用配置文件连接，静态返回connection```java    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");          }      }  

对数据库进行增删改查的测试，主要练习使用PreparedStatement```java 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();  }

}
```