1.Driver接口
- 对于每一个数据库驱动程序都必须实现Driver接口,在编写程序时,当需要连接数据库的时候就需要装载由数据库厂商提供的数据库驱动程序,装载的方式如下:
Class.forname("jdbc.driver_class_name");
- 在程序中不需要直接去访问实现了Driver接口的类,而是由驱动程序管理器类(java.sql.DiverManager)去调用这些Driver实现
Static Conneciton getConnection(String url,String user,String password
注册驱动: 让JDBC知道要使用的是哪个驱动; 获取Connection: 如果可以获取到Connection,那么说明已经与数据库连接上了。Connection对象表示连接,与数据库的通讯都是通过这个对象展开的
import java.sql.*;
public class JdbcTest1 {
private static final String Driver = "com.mysql.cj.jdbc.Driver";//驱动的全类名
private static final String URL = "jdbc:mysql://127.0.0.1:3306/books?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
private static final String DBUSER = "root";
private static final String PASSWORD = "root";
public static void main(String[] args) throws Exception{
Class.forName(Driver);
Connection connection = DriverManager.getConnection(URL,DBUSER,PASSWORD);
String sql = "select * from author";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()){
System.out.println(rs.getString(2));
}
if(connection != null){
connection.close();
}
}
}
2.DriverManager类:是Driver的驱动类
加载数据库驱动程序:Class.forName(driverClass)
通过DriverManager的getConnection()方法获取数据库连接:
Connection connection = DriverManager.getConnection(url,user,password)
DriverManager好处:可以通过重载的getConnection()方法获取数据库连接
可以同时管理多个驱动程序,若注册了多个数据库连接,则调用getConnection()方法时传入的参数不同, 即返回不同的数据库连接
public Connection getConnection() throws Exception{
//1.准备连接数据库的四个字符串
//1>创建Properties对象
Properties properties = new Properties();
//2>获取jdbc.properties对应的输入流
InputStream in = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
//3>加载2>对应的输入流
properties.load(in);
//4>具体决定user,password等4个字符串
String driver = properties.getProperty("driver");
String jdbcUrl = properties.getProperty("jdbcUrl");
String user = properties.getProperty("user") ;
String password= properties.getProperty("password");
//2.加载数据库驱动
Class.forName(driver);
//3.通过getConnection()方法获取数据库连接
Connection connection = DriverManager.getConnection(jdbcUrl,user,password);
return connection;
}
3.Statement类执行更新操作
案例: //通过JDBC像指定的数据表中插入一条记录
public class StstementTest {
private static final String Driver = "com.mysql.cj.jdbc.Driver";
private static final String URL ="jdbc:mysql://127.0.0.1:3306/books?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
private static final String user = "root";
private static final String password = "password";
public Connection getConnection() throws Exception{
Class.forName(Driver);
Connection connection = DriverManager.getConnection(URL,user,password);
return connection;
}
@Test
public void testStatement() throws Exception{
//1.获取数据库连接
Connection conn = getConnection();
//3.准备插入的SQL语句
//插入:String sql ="insert into customers(ID,NAME,EMAIL,BIRTH)" +"values('4','Tom','11233','1995-3-19')";
//删除:String sql = "delete from customers where id=1";
String sql="update customers set NAME ='Mary'" + "where id=4";//修改
//4.执行插入
// 1>获取操作SQL语句的Statement对象,调用Connection的createStstement()方法来获取
Statement statement = conn.createStatement();
//2>调用Statement对象的executeUpdate(sql)执行SQL语句进行插入
statement.executeUpdate(sql);
//5.关闭Statement对象
statement.close();
//2.关闭连接
conn.close();
}
}
注意:1.Statement:用于执行SQL语句的对象
1>通过Connection的createStatement()方法来获取
2>通过executeUpdate(sql)可以执行SQL语句
3>传入的SQL语句可以是insert,update,delete,但不能是select
2.Connection,Statement都是应用程序和数据库服务器的连接资源,使用后一定要关闭,且需要在finally中关闭。
3.先关闭后获取的
4.ResultSet结果集,封装了使用JDBC进行查询的结果
1.调用Statement对象的executeQuery(sql)可以得到结果集
2.ResultSet返回的实际上就是一张数据表,有一个指针指向数据表的第一行的前面
3.可以调用next()方法检测下一行是否有效,若有效返回true,且指针下移,相当于Iterator对象的hasNext()和next()方法的结合体。
4.当指针对应到一行时,可以通过getXxx(index)或getXxx(columnName)获取每一列的值。如,getInt(1),getString("name")
5. ResultSet当然也需要进行关闭
ResultSet接口常用的方法:booleannext(),getString()
public void testResultSet() throws Exception{
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
//1.获取Connection
connection =JdbcTool.getConnection();
//2.获取Statement
statement=connection.createStatement();
//3.准备SQL
String sql = "select ID,NAME ,EMAIL,BIRTH from customers ";//+ " where ID = 2";
//4.执行查询,得到ResultSet
rs=statement.executeQuery(sql);
//5.处理Result
while(rs.next()){
int id =rs.getInt(1);
String name =rs.getString("name");
String email = rs.getString(3);
Date birth = rs.getDate(4);
System.out.println(id);
System.out.println(name);
System.out.println(email);
System.out.println(birth);
}
//6.关闭数据库资源
JdbcTool.release(rs,statement,connection);
}
}
JdbcTool工具类:
/*
* JDBC工具类,封装了一些工具方法
* */
public class JdbcTool {
//1.获取数据库连接的方法
public static Connection getConnection() throws Exception{
String drivaerClass = null;
String jdbcUrl = null;
String user = null;
String password = null;
InputStream in = JdbcTool.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(in);
drivaerClass = properties.getProperty("driver");
jdbcUrl = properties.getProperty("jdbcUrl");
user = properties.getProperty("user");
password = properties.getProperty("password");
Driver driver = (Driver) Class.forName(drivaerClass).newInstance();
Properties info = new Properties();
info.put("user",user);
info.put("password",password);
Connection connection = driver.connect(jdbcUrl,info);
return connection;
}
//2.关闭操作
public static void release(ResultSet rs,Statement statement ,Connection connection){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}