1.原生JDBC操作DDL语句
//使用原生JDBC操作DDL语句 public class Jdbc_Operator_DDL { public static void main(String[] args) throws Exception { //1)导包 //2)注册驱动 Class.forName("com.mysql.jdbc.Driver"); //3)获取数据库的连接对象 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "root" ); //4)sql语句 String sql = "CREATE TABLE student6(id INT PRIMARY KEY AUTO_INCREMENT,"+ " NAME VARCHAR(20), " + " age INT," + " gender VARCHAR(5), " + " address VARCHAR(50), " + " email VARCHAR(50) " + ");"; //5)通过连接对象获取执行对象 Statement stmt = conn.createStatement(); //6)发送到sql数据库 int count = stmt.executeUpdate(sql); System.out.println("count:"+count); //7)释放资源 stmt.close(); conn.close(); } }
2.原生JDBC操作DML语句
//使用JDBC操作DML语句 public class Jdbc_Operator_DML { public static void main(String[] args) throws Exception { //1)导包 //2)注册驱动 Class.forName("com.mysql.jdbc.Driver"); //3)获取数据库的连接对象 Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/student?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "root" ); //4)sql语句 String sql = "INSERT INTO student6(NAME,age,gender,address,email) " + "VALUES('文章',35,'男','西安市','wenzhang@163.com'), " + "('王宝强',34,'男','北京市','wangbaoqiang@163.com')," + "('黄晓明',36,'男','北京市','hxm@163,com');" ; //5)通过连接对象获取执行对象 Statement stmt = conn.createStatement(); //6)执行sql int count = stmt.executeUpdate(sql); System.out.println("影响了"+count+"行"); //7)释放资源 stmt.close(); conn.close(); } }
3.封装工具类,简化jdbc操作DDL/DML语句的书写格式
//封装工具类 public class JdbcUtils { private static String url = null; private static String user = null; private static String password = null; private static String driverClass = null; //静态代码块:完成注册驱动 static { try { //1)创建属性集合列表Properties Properties prop = new Properties(); //2)读取配置文件,获取资源文件所在的输入流对象 InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"); //3)将资源文件所在的输入流对象的数据加载到属性集合列表中 prop.load(inputStream); //4)通过配置文件的key获取他们所对应的值 url = prop.getProperty("url"); driverClass = prop.getProperty("driverClass"); user = prop.getProperty("user"); password = prop.getProperty("password"); //5)注册驱动 Class.forName(driverClass); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //静态功能的方法 public static Connection getConnection() { Connection connection = null; try { connection = DriverManager.getConnection(url, user, password); return connection; } catch (SQLException e) { e.printStackTrace(); } return null; } //将释放资源的过程,定义到这个方法中 public static void close(Statement stmt, Connection conn) { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
//将id = 4的学生姓名改为"王力宏" public class JdbcUtilsTest { public static void main(String[] args) { Connection connection = null; Statement stmt = null; try { //获取连接对象 connection = JdbcUtils.getConnection(); //sql语句 String sql = "update student6 set name = '王力宏' where id = 3" ; //获取执行对象 stmt = connection.createStatement(); //执行 int count = stmt.executeUpdate(sql); System.out.println("影响了:"+count+"行"); } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.close(stmt,connection); } } }
4.使用ResultSet查询所有结果
//学生类 public class Student { private int id ;//学生编号 private String name ; //学生姓名 private int age ;//学生年龄 private String gender ;//学生的性别 private String address ; //地址 private String email ;// 邮箱 public Student() { } public Student(int id, String name, int age, String gender, String address, String email) { this.id = id; this.name = name; this.age = age; this.gender = gender; this.address = address; this.email = email; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + ", gender='" + gender + '\'' + ", address='" + address + '\'' + ", email='" + email + '\'' + '}'; } }
//工具类 public class JdbcUtils2 { private static String url = null; private static String user = null; private static String password = null; private static String driverClass = null; //静态代码块:完成注册驱动 static { try { //1)创建属性集合列表Properties Properties prop = new Properties(); //2)读取配置文件,获取资源文件所在的输入流对象 InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"); //3)将资源文件所在的输入流对象的数据加载到属性集合列表中 prop.load(inputStream); //4)通过配置文件的key获取他们所对应的值 url = prop.getProperty("url"); driverClass = prop.getProperty("driverClass"); user = prop.getProperty("user"); password = prop.getProperty("password"); //5)注册驱动 Class.forName(driverClass); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //静态功能的方法 public static Connection getConnection() { Connection connection = null; try { connection = DriverManager.getConnection(url, user, password); return connection; } catch (SQLException e) { e.printStackTrace(); } return null; } //重载close的方法 public static void close(ResultSet rs, Statement stmt, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
//查询当前库中的学生的所有信息,将信息打印在控制台上 public class Jdbc_Operator_DQL { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { //1)获取连接对象 conn = JdbcUtils.getConnection(); //2)sql语句 String sql = "select * from student6"; //3)获取执行对象 stmt = conn.createStatement(); //4)执行sql语句 rs = stmt.executeQuery(sql); System.out.println("查询结果为:"); //方式1:通过列的索引值获取:第一列为1,第二列为2 while (rs.next()){ //XXX getXXX(int columnIndex) /*int id = rs.getInt(1); String name = rs.getString(2); int age = rs.getInt(3); String gender = rs.getString(4); String address = rs.getString(5); String email = rs.getString(6);*/ //方式2:通过列的名获取 //XXX getXXX(String columnLaber) int id = rs.getInt("id"); String name = rs.getString("name"); int age = rs.getInt("age"); String gender = rs.getString("gender"); String address = rs.getString("address") ; String email = rs.getString("email"); System.out.println(id+"\t"+name+"\t"+age+"\t"+gender+"\t"+address+"\t"+email); } } catch (SQLException e) { e.printStackTrace(); }finally { //释放资源 JdbcUtils2.close(rs,stmt,conn); } } }