Java基础:数据库连接 操作 增删改查
创建表
在数据库中创建表
idea导入jar包
官网下载 mysql-connector-java.jar
在idea中新建lib包,将mysql-connector-java.jar复制到lib包下
代码
import java.sql.*;
public class test {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//加载驱动...mysql6.0之后需要加 cj
Class.forName("com.mysql.cj.jdbc.Driver");
//用户信息和url
String url = "jdbc:mysql://localhost:3306/表名?serveTimezone=GMT%2B8";
String username = "root";
String password = "mysql密码";
//连接数据库
Connection connection = DriverManager.getConnection(url, username, password);
//创建对象,用来执行SQL
Statement statement = connection.createStatement();
//SQL语句
String sql = "SELECT * FROM user"; //查询user表下的内容
//执行SQL语句
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("age= "+ resultSet.getObject("age"));
System.out.println("==================");
}
//释放资源
resultSet.close();
statement.close();
connection.close();
}
}
代码的加载驱动、用户信息及连接数据库等内容在后续的程序中都是重复的,所以可以创建工具类用来存放这些内容
工具类
在工程中新建一个util包,包中新建一个类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DbUtil {
private static String jdbcName = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/表名?serveTimezone=GMT%2B8";
private static String userName = "root";
private static String userPwd = "mysql密码";
private static Connection con;
public static Connection getCon() throws Exception {
Class.forName(jdbcName);
con = DriverManager.getConnection(url, userName, userPwd);
return con;
}
public static void close(ResultSet res, Statement sta,Connection con) throws Exception {
if (res != null){
res.close();
}
if (sta != null){
sta.close();
}
if (con != null) {
con.close();
}
}
}
增
创建工具类后,可减少重复部分
import util.DbUtil;
import java.sql.Connection;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) throws Exception {
Connection connection = DbUtil.getCon();
Statement statement = connection.createStatement();
String sql = " INSERT INTO `user`(`name`,`age`) VALUES ('张三','13')";
int i = statement.executeUpdate(sql);
if (i>0) {
System.out.println("添加成功");
}
DbUtil.close(null,statement,connection);
}
}
删
import com.hebeu.util.DbUtil;
import java.sql.Connection;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) throws Exception {
Connection connection = DbUtil.getCon();
Statement statement = connection.createStatement();
String sql = " DELETE FROM `user` WHERE id = 1";
int i = statement.executeUpdate(sql);
if (i>0) {
System.out.println("删除成功");
}
DbUtil.close(null,statement,connection);
}
}
改
import com.hebeu.util.DbUtil;
import java.sql.Connection;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) throws Exception {
Connection connection = DbUtil.getCon();
Statement statement = connection.createStatement();
String sql = "UPDATE `user` SET `name` = '张三' WHERE id = 1";
int i = statement.executeUpdate(sql);
if (i>0) {
System.out.println("更新成功");
}
DbUtil.close(null,statement,connection);
}
}
查
import com.hebeu.util.DbUtil;
import java.sql.Connection;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) throws Exception {
Connection connection = DbUtil.getCon();
Statement statement = connection.createStatement();
String sql = "SELECT * FROM user";
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("age= "+ resultSet.getObject("age"));
System.out.println("==================");
}
DbUtil.close(null,statement,connection);
}
}
以上为本人学习Java时的一些笔记以及自己的理解
如有不足之处请大家指正