连接数据库
1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
本行代码主要作用是加载(注册)数据库驱动(到JVM)
2.创建链接
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=true&characterEncoding=utf8","root","123");
System.out.println("创建链接成功");
通过connection与DriverManager建立与数据库的连接
对数据库的操作
查询账户操作
3.创建sql语句
String sql = "select * from user";
4.得到statement对象
statement = connection1.prepareStatement(sql);
5.执行sql语句,得到结果集
resultSet = statement.executeQuery();
6.处理结果集
while(resultSet.next()){
int Uno = resultSet.getInt("id");
String Uname = resultSet.getString("username");
String Upwd = resultSet.getString("password");
System.out.println("用户编号:\t" + Uno + " 用户名:\t" + Uname + " 用户密码:\t" + Upwd);
}
7.关闭对象,回收数据库资源
if(resultSet!=null){
try {
resultSet.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
关闭结果集-->关闭数据库操作对象-->关闭连接
结果
增添账户操作
完整代码
public class insert {
public static void main(String[] args){
PreparedStatement statement=null;
Connection connection=null;
//1.加载驱动
try{
Class.forName("com.mysql.jdbc.Driver");
//2.创建连接
Connection connection1 = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=true&characterEncoding=utf8","root","123");
System.out.println("创建链接成功");
Scanner sc2=new Scanner(System.in);
Scanner sc3=new Scanner(System.in);
//3.创建sql语句
String sql="insert into user (id,username,password) values(?,?,?)";
//4.得到statement对象
System.out.print("请输入要添加的用户编号:");
int Uno = sc2.nextInt();
System.out.print("请输入要添加的用户名:");
String Uname = sc3.nextLine();
System.out.print("请输入要添加的用户密码:");
String Upwd = sc3.nextLine();
statement=connection1.prepareStatement(sql);
statement.setInt(1,Uno);
statement.setString(2,Uname);
statement.setString(3,Upwd);
statement.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
if(statement!=null){
try {
statement.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
}
结果
修改用户操作
核心代码
Scanner upd1=new Scanner(System.in);
Scanner upd2=new Scanner(System.in);
//3.创建sql语句
String updSql="update user set username=? , password=? where id=?";
//4.得到statement对象
statement2=connection2.prepareStatement(updSql);
System.out.print("请输入要修改的用户编号:");
int aid = upd1.nextInt();
System.out.print("请输入修改后的用户名:");
String un = upd2.nextLine();
System.out.print("请输入修改后的用户密码:");
String pw = upd2.nextLine();
statement2.setString(1,un);
statement2.setString(2,pw);
statement2.setInt(3,aid);
statement2.executeUpdate();
结果
删除用户操作
核心代码
Scanner del=new Scanner(System.in);
//3.创建sql语句
String delSql="delete from user where (id)=(?)";
//4.得到statement对象
System.out.print("请输入要删除的用户编号:");
int id = del.nextInt();
statement1=connection.prepareStatement(delSql);
statement1.setInt(1,id);
statement1.executeUpdate();
结果
连接数据库与回收数据库资源的封装
在src下新建util包,新建一个DBUtil类用来封装各种操作同样需求的操作。
public class DBUtil {
public static Connection getConnection(){
try {
Class.forName("com.mysql.jdbc.Driver");
//2.创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=true&characterEncoding=utf8","root","123");
return conn;
}catch(Exception e){
e.printStackTrace();
}
return null;
}
public static void close(ResultSet resultSet, Statement statement,Connection connection){
if(resultSet!=null){
try {
resultSet.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
封装后便可将原有的代码修改,形式变得更加简洁:
public class JDBC {
public static void main(String[] args){
ResultSet resultSet=null;
PreparedStatement statement=null;
Connection connection=null;
//1.加载驱动
try{
Connection connection1= DBUtil.getConnection();
System.out.println("创建链接成功");
//3.创建sql语句
String sql = "select * from user";
//4.得到statement对象
statement = connection1.prepareStatement(sql);
//5.执行sql 得到结果集
resultSet = statement.executeQuery();
//6.处理结果集
while(resultSet.next()){
int Uno = resultSet.getInt("id");
String Uname = resultSet.getString("username");
String Upwd = resultSet.getString("password");
System.out.println("用户编号:\t" + Uno + " 用户名:\t" + Uname + " 用户密码:\t" + Upwd);
//System.out.println(resultSet.getInt("id" ));
//System.out.println(resultSet.getString( "username"));
//System.out.println(resultSet.getString( "password"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil.close(resultSet,statement,connection);
}
}
}