Day38-jdbc相关基础操作
学习jdbc相关基础操作(增删改查)
JDBC （Java Database Connectivity）
它是一种标准Java应用编程接口（ JAVA API），用来连接 Java 编程语言和广泛的数据库。
- 导入依赖包(这里使用的是 mysql-connector-java-5.1.49.jar)添加依赖
- 最原始的方法,添加
jdbc:mysql://localhost:端口号/数据库名字
端口号为本地localhost:3306可以写成如下所示:
jdbc:mysql:///数据库名字
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) throws Exception {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接 参数url user(用户名) password(密码)
Connection connection = DriverManager.getConnection("url", "user", "password");
//创建连接
Statement statement = connection.createStatement();
//执行语句返回结果
String sql = "insert into grade (gradeid,gradename) values (4,'H5')";
int resultSet = statement.executeUpdate(sql);
System.out.println(resultSet);
//关闭
statement.close();
connection.close();
}
}
- 更新
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) throws Exception {
//1 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接 使用Unicode编码,字符集设为utf-8
Connection connection = DriverManager.getConnection("url?useUnicode=true&characterEncoding=utf8", "user", "password");
//创建发送sql的对象
Statement statement = connection.createStatement();
//发送sql并处理结果
int result = statement.executeUpdate("update grade set gradename = 'java高级' where gradeid = 1");
//处理结果
System.out.println("更新成功:===>"+result);
//关闭连接
statement.close();
connection.close();
}
}
- 删除
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) throws Exception{
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
Connection connection = DriverManager.getConnection("url?useUnicode=true&characterEncoding=utf8","user","password");
//创建执行sql对象
Statement statement = connection.createStatement();
//执行sql并获取返回结果
int result = statement.executeUpdate("delete from grade where gradeid = 4");
//处理返回结果
System.out.println("删除成功===>"+result);
//关闭连接
statement.close();
connection.close();
}
}
- 发现重复代码较多,进行封装操作...
import java.sql.*;
public class DBUtils {
/**
* 重复代码较多,进行封装
* 使用静态代码块,原因:类的字节码文件在程序中只有一份,不管加载多少次都是一份,
* 没有必要每次创建连接的时候加载,只需要程序启动时加载一次即可
*/
//注册驱动
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//得到连接
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection("url?useUnicode=true&characterEncoding=utf8", "user", "password");
return connection;
}
//关闭操作重复也比较多,将其封装
public static void closeResources(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();
}
}
}
}
- 增删改写完,更多用到的还是查询
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestQuery {
public static void main(String[] args) throws SQLException {
Connection connection = DBUtils.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from grade");
//循环遍历数据
//不清楚多少条,用while循环
while (resultSet.next()) {
//得到id和name 内部返回结果通过列的id获取数据getXXX的类型和下标id的列的类型要保持一致
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
System.out.println("主键-->"+id+" 班级名-->"+name);
}
//关闭游标集合
// resultSet.close();
//关闭连接
// statement.close();
// connection.close();
DBUtils.closeResources(connection,statement,resultSet);
}
}
- 发现根据下标不容易知道每个下标是什么类型,修改为使用列名方式获取数据
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 根据下标不容易知道每个下标是什么类型的,修改为使用列名获取数据
*/
public class TestQuery02 {
public static void main(String[] args) throws SQLException {
Connection connection = DBUtils.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from grade");
//获取列名
while (resultSet.next()) {
//获取的时候获取的是列名,不要搞错类型,取决于创建数据表的类型
int gradeid = resultSet.getInt("gradeid");
String gradename = resultSet.getString("gradename");
System.out.println("主键-->"+gradeid+" 课程名-->"+gradename);
}
//关闭 每次关闭的操作也重复了,封装一下
// resultSet.close();
// statement.close();
// connection.close();
DBUtils.closeResources(connection,statement,resultSet);
}
}
-
经过上面的两个查询的例子,发现我们的资源关闭每次都需要关闭,因为修改了DBUtils 添加了关闭资源(closeResources)的方法 resultset没有就写null,见DBUtils...
-
前面例子返现sql语句都是写死的,条件固定,通过传递条件拼接sql的方式进行查询,模拟用户登录操作
mport java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* 前面例子sql语句是写死的,条件也是固定的我么可以通过传递条件拼接sql的方式来进行查询
* 将输入的用户名密码拼接到参数当中 控制台输入
*/
public class TestLogin {
public static void main(String[] args) throws SQLException {
Connection connection = DBUtils.getConnection();
Statement statement = connection.createStatement();
String username = null;
String password = null;
//获取输入
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名:");
username = scanner.nextLine();
System.out.println("请输入密码:");
password = scanner.nextLine();
//???为输入内容
// statement.executeQuery("select * from user where username = ??? and password = ???");
//需要注意,在拼接的时候要加上单引号'',否则会把值当作列来进行判断
ResultSet resultSet = statement.executeQuery("select * from user where username ='" + username + "' and password = '" + password + "' ");
//可以将username设为空 password设为空,开始拼接 username = "+usrname+"...
if (resultSet.next()) {
//游标移动,有结果,能够查询到,登录成功
System.out.println("登录成功");
}else {
//登录失败
System.out.println("账号或者密码错误");
}
//关闭连接
DBUtils.closeResources(connection,statement,resultSet);
}
}
- 经过查询传参测试,测试一下插入更新和删除能不能通过拼接sql来写
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* 插入是否可以拼接?
*/
public class TestInsertScanner {
public static void main(String[] args) throws SQLException {
String gradeid = null;
String gradename = null;
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要插入班级id");
gradeid = scanner.nextLine();
System.out.println("请输入要插入班级名称");
gradename = scanner.nextLine();
Connection connection = DBUtils.getConnection();
Statement statement = connection.createStatement();
int result = statement.executeUpdate("insert into grade (gradeid,gradename) values ("+gradeid+",'"+gradename+"');");
DBUtils.closeResources(connection,statement,null);
}
}
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class TestUpdateScanner {
public static void main(String[] args) throws SQLException {
String gradeid = null;
String gradename = null;
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要修改的id:");
gradeid = scanner.nextLine();
System.out.println("请输入要修改的课程名:");
gradename = scanner.nextLine();
Connection connection = DBUtils.getConnection();
Statement statement = connection.createStatement();
statement.executeUpdate("update grade set gradename = '"+gradename+"' where gradeid = "+gradeid+" ");
DBUtils.closeResources(connection,statement,null);
}
}
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class TestDeleteScanner {
public static void main(String[] args) throws SQLException {
String gradeid = null;
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要删除的课程id");
gradeid = scanner.nextLine();
Connection connection = DBUtils.getConnection();
Statement statement = connection.createStatement();
int result = statement.executeUpdate("delete from grade where gradeid = "+gradeid+" ");
DBUtils.closeResources(connection,statement,null);
}
}
- 发现也是可以的
总结一下:
1.jdbc连接数据库步骤:
注册加载驱动
获取连接
声明创建连接
执行sql语句返回结果
关闭
2.拼接sql是注意加单引号(依据要传参格式来)
3.在获得连接时,要确保url,user,password正确
浙公网安备 33010602011771号