发布于2021-06-12 14:57 阅读(557) 评论(0) 点赞(21) 收藏(0)
驱动:声卡,显卡,数据库
我们的程序会通过数据库驱动,和数据库打交道!
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(java操作数据库的)规范,生成JDBC,这些规范的实现由具体的厂商去做!
对于开发人员,只需要掌握JDBC接口的操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar
创建测试数据库
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','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04');
1.创建一个普通项目
2.在该项目创建lib目录,导入数据库驱动
3.导入成功后,鼠标右键点击lib,点击Add as Library
4.编写代码测试
package com.jialidun.test;
import java.sql.*;
public class JdbcFirstDemo {
public static void main(String[] args) throws Exception {
//1、加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2、用户信息和URL
//useUnicode=true 使用Unicode编码保证
// characterEncoding=utf8 编码使用utf8
// useSSL=true 使用安全的链接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimeZone=UTC2B8";
String username = "root";
String password = "root";
//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("password="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
DriverManager
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//connection代表数据库
//数据库设置自动提交
connection.setAutoCommit();
//事务提交
connection.commit();
//事务回滚
connection.rollback()'
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimeZone=UTC2B8";
//mysql -- port:3306
// jdbc:mysql://主机地址ip:端口号/数据库名?参数1&参数2
//oracle -- port:1521
//jdbc:oracle:this:@localhost:1521:sid
Statement 执行SQL的对象 PrepareStatement执行SQL的对象
String sql = "SELECT * FROM users";
statement.executeQuery(sql);//查询操作返回ResultSet
statement.execute(sql);//执行任何SQL
statement.executeQuery(sql);//更新、添加、删除都是用这个,返回一个受影响的行数
ResultSet查询的结果集:封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getDouble();
...
遍历,指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
//6、释放连接、耗资源,用完关掉!
resultSet.close();
statement.close();
connection.close();
JDBC中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的SQL语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultesultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement st = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num > 0){
System.out.println("插入成功!");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement st = connection.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num > 0){
System.out.println("删除成功!");
}
CRUD操作-update
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement st = connection.createStatement();
String sql = " update user set name='' where name='' ";
int num = st.executeUpdate(sql);
if(num > 0){
System.out.println("修改成功!");
}
CRUD操作-read
使用executeQuery(String sql)方法完成数据添加操作,示例操作:
Statement st = connection.createStatement();
String sql = " select * from user where id = 1 ";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}
代码实现
1.提取工具类
package com.jialidun.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
try {
InputStream in =
JdbcUtils.class.getClassLoader().getResourceAsStream("jb.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 (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放连接资源
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
工具类配置
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
username=root
password=root
2.编写增删改的方法executeUpdate
package com.jialidun.test;
import com.jialidun.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
String sql = null;
try {
//获取连接
connection = JdbcUtils.getConnection();
//获取statement传输器
statement = connection.createStatement();
sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(4,'rygar','123456','rygar_h@163.com','2021-06-08')";
int num = statement.executeUpdate(sql);
if(num > 0){
System.out.println("添加用户成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
//释放连接
JdbcUtils.close(connection,statement,null);
}
}
}
package com.jialidun.test;
import com.jialidun.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
//获取连接
connection = JdbcUtils.getConnection();
//获取传输器
statement = connection.createStatement();
String sql = "DELETE FROM users WHERE id=4";
//执行SQL
int num = statement.executeUpdate(sql);
if (num > 0){
System.out.println("删除用户成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
//释放连接
JdbcUtils.close(connection,statement,null);
}
}
}
package com.jialidun.test;
import com.jialidun.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
//获取连接
connection = JdbcUtils.getConnection();
//获取传输器
statement= connection.createStatement();
String sql = "UPDATE users SET `name`='rygar',`email`='rygar_h@163.com' WHERE id=1";
int num = statement.executeUpdate(sql);
if(num> 0){
System.out.println("修改用户成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
//释放连接
JdbcUtils.close(connection,statement,null);
}
}
}
package com.jialidun.test;
import com.jialidun.utils.JdbcUtils;
import com.sun.xml.internal.bind.v2.model.core.ID;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestRead {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
//获取连接
connection = JdbcUtils.getConnection();
//创造传输器
statement = connection.createStatement();
//执行sql
String sql = "select * from users where id=1";
rs = statement.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
System.out.println(rs.getString("email"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
//释放连接
JdbcUtils.close(connection,statement,rs);
}
}
}
```![在这里插入图片描述](https://img-blog.csdnimg.cn/20210608192315907.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzUxMDY3MDQ3,size_16,color_FFFFFF,t_70)
作者:我很伤感
链接:http://www.javaheidong.com/blog/article/222430/6d3ef7af49d5ba3b8d6f/
来源:java黑洞网
任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任
昵称:
评论内容:(最多支持255个字符)
---无人问津也好,技不如人也罢,你都要试着安静下来,去做自己该做的事,而不是让内心的烦躁、焦虑,坏掉你本来就不多的热情和定力
Copyright © 2018-2021 java黑洞网 All Rights Reserved 版权所有,并保留所有权利。京ICP备18063182号-2
投诉与举报,广告合作请联系vgs_info@163.com或QQ3083709327
免责声明:网站文章均由用户上传,仅供读者学习交流使用,禁止用做商业用途。若文章涉及色情,反动,侵权等违法信息,请向我们举报,一经核实我们会立即删除!