本文详细介绍了如何在Java中使用JDBC进行数据库操作，包括连接数据库、执行增删改查SQL语句，以及如何封装一个基础的数据库操作工具类BaseDao，以简化后续操作。
package com.demo;
import java.sql.*;
public class Demo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2、创建连接（DriverManager驱动管理器，创建连接）
Connection conn = DriverManager.getConnection("jdbc:mysql:///bjo1", "root", "root");
//3、sql语句执行 statement用来执行sql的对象
Statement statement = conn.createStatement();
//执行更新（增加、删除、修改）返回int 数据代表返回更新的行数（大于0代表有数据被更新）
int i = statement.executeUpdate("delete from admin where id = 1");
//执行查询，返回ResultSet结果集
ResultSet rs = statement.executeQuery("select from admin where id = 1");
//4、返回结果
//循环结果集rs.next()循环遍历
while(rs.next()){
//取值方式
//getInt（1） 获取数据库中int类型数据，第一列的值
rs.getInt(1);
//getInt（"id"） 获取数据库中int类型数据，列名叫”id“的这一列的值
rs.getInt("id");
}
//5、关闭资源
rs.close();
statement.close();
conn.close();
}}
使用JDBC实现添加功能。
package com.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class InsertDemo {
public static void main(String[] args) {
Connection conn = null;
Statement statement = null;
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名：");
String name = sc.next();
System.out.println("请输入密码：");
String pw1 = sc.next();
//1、加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
//2、创建连接
/*
ur1:数据库连接路径
user：用户名
password：密码
jdbc:mysql://服务器地址：端口号/数据库名字
jdbc:mysql://localhost:3306/news_week1
jdbc:mysql:///news_week1
ClassNotFoundException异常：类没找到
SQLException异常：SQL异常
*/
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/news_week1?characterEncoding=utf-8", "root", "root");
//3、获取执行SQL语句的对象
statement = conn.createStatement();
//4、添加
String sql = "insert into admin values(null,'"+name+"','"+pw1+"')";
//执行sql语句
int i = statement.executeUpdate(sql);
//执行更新操作，返回int类型参数(返回执行更新的行数)
if (i > 0){
//添加成功
System.out.println("添加成功！");
}else {
System.out.println("添加失败");
}
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
try {
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
使用JDBC实现修改功能
package com.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;
public class UpdateDemo {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入需要修改的用户名：");
String name = sc.next();
System.out.println("请输入需要修改的密码：");
String pwd = sc.next();
//1、加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
//2、创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql:///news_week1?characterEncoding=utf-8", "root", "root");
//3、获取执行SQL语句的对象
Statement statement = conn.createStatement();
//4、更新密码
int i = statement.executeUpdate("update admin set admin_pwd = '"+pwd+"' where admin_name = '"+name+"'");
//5、判断是否成功
if (i > 0 ){
System.out.println("修改成功！");
}else{
System.out.println("修改失败！");
}
statement.close();
conn.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
使用JDBC实现删除功能
package com.demo;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class DeleteDemo {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入需要删除的编号：");
String id = sc.next();
Connection conn = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///news_week1", "root", "root");
statement = conn.createStatement();
int i = statement.executeUpdate("delete from admin where admin_id = "+id);
if (i > 0) {
System.out.println("删除成功！");
} else {
System.out.println("删除失败！");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
statement.close();
conn.close();
}catch (Exception e){
}
}
}
}
如何封装数据库操作的工具类 BaseDao
package com.demo;
import java.sql.*;
public class BaseDao {
//定义连接JDBC需要的常量
public static final String driver = "com.mysql.jdbc.Driver";
protected static final String url = "jdbc:mysql://localhost:3306/news_week1?characterEncoding=utf-8";
public static final String user = "root";
public static final String pwd = "root";
static Connection conn = null;
static Statement statement = null;
static ResultSet rs = null;
//1、加载驱动
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}//2、创建连接
public static Connection getConn() {
try {
conn = DriverManager.getConnection(url, user, pwd);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return conn;
}
public static void close() {
try {
if (rs != null) {
rs.close();
}if (statement != null) {
statement.close();
}if (conn != null) {
conn.close();
}
}catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
调用BaseDao的方法实现添加和查询功能
package com.demo;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class Test {
@org.junit.Test
public void select() {
//通过工具类加载驱动创建连接（类加载时执行静态代码块）
Connection conn = BaseDao.getConn();
//创建可执行sql语句的对象
try {
Statement statement = conn.createStatement();
String sql = "select * from admin";
ResultSet rs = statement.executeQuery(sql);
ArrayList<Admin> list = new ArrayList<>();
//循环遍历结果集并展示while遍历结果 re.next()
while (rs.next()) {
Admin admin = new Admin(rs.getInt(1), rs.getString(2), rs.getString(3));
list.add(admin);
}
for (Admin admin : list) {
System.out.println(admin);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@org.junit.Test
public void insert() throws SQLException {
Connection conn = BaseDao.getConn();
Statement statement = conn.createStatement();
String sql = "insert into admin values(null,'小米','2313')";
int i = statement.executeUpdate(sql);
if (i > 0 ){
System.out.println("添加成功！");
}else{
System.out.println("添加失败！");
}
BaseDao.close();
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
