DriverManager
//1.加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//推荐这种写法加载驱动
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, userName, passWord);
// connection代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.setAutoCommit(true);
connection.commit();
connection.rollback();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
// mysql默认端口3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
// oracle默认端口1521
// jdbc:oracle:thin:@localhost:1521:sid
Statement 执行sql对象 、 PreparedStatement 执行sql对象
String sql = "SELECT * FROM users";//编写SQL
statement.executeQuery();//执行查询 返回ResultSet
statement.executeUpdate();//新增,删除,修改,都用这个,返回受影响的行数
statement.execute();//执行任何SQL
ResultSet 查询的结果集,封装了所有的查询结果
获得指定的数据类型
//在不知道列类型的情况下使用
resultSet.getObject();
//如果知道列类型,就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getDouble();
resultSet.getBigDecimal();
resultSet.getFloat();
resultSet.getDate();
//...
遍历,指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();//消耗资源
statement对象
jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的SQL语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发送了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("插入成功~");
}
CRUD操作-delete
Statement statement = connection.createStatement();
String sql = "delete from user where id=1";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("删除成功~");
}
CRUD操作-update
Statement statement = connection.createStatement();
String sql = "update user set name='' where name =''";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("修改成功~");
}
CRUD操作-read
Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
//根据获取列的数据类型,分别调用resultSet的相应方法映射到java对象中
}
例:
提取工具类
配置文件db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
JdbcUtils
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//驱动只用加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
/**
* 释放资源
*/
public static void release(Connection con, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
编写增、删、改的方法,executeUpdate
import com.yuan.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
st = con.createStatement();
String sql = "UPDATE INTO users(`id`,`name`,`password`,`email`,`birthday`)\n" +
"VALUES (4,'牛六','123456','niuliu@sina.com','1988-12-04')";
int num = st.executeUpdate(sql);
if (num > 0) {
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(con,st,rs);
}
}
}
import com.yuan.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
st = con.createStatement();
String sql = "DELETE FROM users WHERE `id`=4";
int num = st.executeUpdate(sql);
if(num > 0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
}
import com.yuan.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "UPDATE users SET birthday='1990-12-01' WHERE id=1";
int num = statement.executeUpdate(sql);
if (num > 0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
查询
import com.yuan.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "SELECT * FROM users WHERE id=1";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("id="+resultSet.getInt("id"));
System.out.println("name="+resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}