数据库驱动
顾名思义，数据库驱动是应用程序和数据库存储之间的一种接口，数据库厂商为了某一种
开发语言环境（比如Java，C）能够实现数据库调用而开发的类似翻译员功能的程序，将复杂
的数据库操作与通信抽象成为了当前开发语言的访问接口。
JDBC
SUN公司为了简化开发人员的操作，提供了一个（Java操作数据库）的规范，俗称JDBC。
这些规范的实现由具体的厂商去做~
需要导入一个数据库驱动包，数据库什么版本就下什么版本，我们这里是5.1.47的驱动
第一个JDBC程序
1.创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci; USE jdbcStudy; CREATE TABLE `users`( id INT PRIMARY KEY, NAME VARCHAR(40), PASSWORD VARCHAR(40), email VARCHAR(60), birthday DATE ); INSERT INTO `users`(id,NAME,PASSWORD,email,birthday) VALUES(1,'zhansan','123456','[email protected]','1980-12-04'), (2,'lisi','123456','[email protected]','1981-12-04'), (3,'wangwu','123456','[email protected]','1979-12-04')
2.创建一个普通项目
3.导入数据库驱动
新建lib文件夹，将驱动的jar包放进去，并add library
4.编写测试代码
//我的第一个JDBC程序
public class jdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2.用户信息
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName="root";
String password="123456";
//3.连接成功，数据库对象,代表数据库
Connection connection = DriverManager.getConnection(url, userName, password);
//4.执行SQL的对象
Statement statement = connection.createStatement();
//5.执行SQL
String sql="select * from users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("NAME"));
System.out.println("pwd="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birth="+resultSet.getObject("birthday"));
System.out.println("==============================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
statement对象
JDBC中statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库
发送增删改查语句即可。
statement对象的executeUpdate方法，用于向数据库发送增删改的SQL语句，executeUpdate执行完成后，将会返回
一个整数（即增删改语句导致了数据库j几行数据发生了变化）
statement对象的executeQuery方法，用于向数据库发送查询语句，executeQuery方法返回代表查询结果的resultSet对象
1.CRUD操作-insert
Statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num > 0){
System.out.println("插入成功！");
}
2.CRUD操作-delete
Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num > 0){
System.out.println("删除成功！");
}
3.CRUD操作-update
Statement st = conn.createStatement();
String sql = "update user set name = '..' where name = '..';
int num = st.executeUpdate(sql);
if(num > 0){
System.out.println("修改成功！");
}
4.CRUD操作-select
Statement st = conn.createStatement();
String sql = "select * from user where id = 1";
ResultSet rs = st.executeQuery(sql);
while(rs.next){
System.out.println(rs.getObject("id"));
....
}
提取工具类
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");
//1.加载驱动只用加载一次
Class.forName(driver);
}catch (Exception e){
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放资源
public static void release(Connection conn, Statement st, ResultSet rs){
if (rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (rs!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
INSERT
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st =null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,birthday)"+
"values(4,'xiaolei','33312','[email protected]','2022-02-02')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功！！！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
UPDATE
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs= null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "update users set name = '憨憨' where name = 'wangwu'";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("更新成功！！！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
DELETE
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "delete from users where id =4";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("删除成功！！！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SELECT
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement st= null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "select * from users";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("NAME"));
System.out.println("password="+rs.getObject("PASSWORD"));
System.out.println("email="+rs.getObject("email"));
System.out.println("birthday="+rs.getObject("birthday"));
System.out.println("+++++++++++++++++++++++");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
标签：JDBC,String,rs,数据库,System,st,conn,out From： https://www.cnblogs.com/zhulei118/p/16992209.html