JDBC (mysql)数据库查询步骤,实现基本数据功能
开始前的准备事项
(1)去MySQL官网安装下载程序jar包,每个版本对应着不同版本的jar包,下载时看好,下载后在eclipse的工程中创建一个lib文件夹,将jar包复制进去并导入。
(2)确保数据库本地服务器处于开启状态,并创建相应的数据库,表,字段,并写入一些初始数据。
JDBC数据库实现增删改查步骤
(一)注册驱动:Class.forName(“com.mysql.cj.jdbc.Driver”);
(二)获取连接:
1.要连接的数据库的url—>String url = “jdbc:mysql://localhost:3306/school?serverTimezone=GMT”;
2.用户名—>String username = “root”;
3.密码 —>String password = “admin”;
(三)创建Connection连接对象 —> Connection connection = DriverManager.getConnection(url,username,password);
(三)创建statement对象—> Statement statement = connection.createStatement();
(四)执行sql语句—> String sql =“select * from student”;
(五)关闭资源—> connection.close();
statement.close();
代码中出现的异常,全部thorws抛出,不进行try捕获
源代码
package com.jdbctest01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.omg.CORBA.PUBLIC_MEMBER;
public class JdbcMysqlTest {
String username = "root";
String password = "admin";
Connection connection;
Statement statement;
String url = "jdbc:mysql://localhost:3306/school?serverTimezone=GMT";
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// TODO Auto-generated method stub
JdbcMysqlTest jdbcMysqlTest = new JdbcMysqlTest();
//1、清空表中所有数据
jdbcMysqlTest.deleteAllData();
//2、增加数据
jdbcMysqlTest.insertData("李晓帅", "2020001", "山东", "20");
jdbcMysqlTest.insertData("张一", "20210001", "烟台蓬莱", "19");
jdbcMysqlTest.insertData("张二", "20210002", "烟台蓬莱", "20");
jdbcMysqlTest.insertData("张三", "20210003", "烟台蓬莱", "21");
jdbcMysqlTest.insertData("张四", "20210004", "烟台蓬莱", "22");
//3、查询数据:显示所有记录
jdbcMysqlTest.selectAllData();
//4、修改数据:根据学号 20210004 修改年龄为18
jdbcMysqlTest.updateData("20210004","18");
//5、查询数据:显示所有记录
jdbcMysqlTest.selectAllData();
//6、删除数据:根据学号 20210003 删除记录
jdbcMysqlTest.deleteData("20210003");
//查询数据:显示所有记录
jdbcMysqlTest.selectAllData();
}
//全部删除语句模块
public int deleteAllData() throws SQLException, ClassNotFoundException{
int i = 0;
Class.forName("com.mysql.cj.jdbc.Driver");
String sql ="delete from student";
connection = DriverManager.getConnection(url,username,password);
statement = connection.createStatement();
i = statement.executeUpdate(sql);
if (i==1) {
System.out.println("删除数据成功");
}else {
System.out.println("删除数据失败");
}
connection.close();
statement.close();
return i;
}
//插入语句模块
public int insertData(String name,String sno,String addr,String age ) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String sql ="insert into student values('"+name+"','"+sno+"','"+addr+"','"+age+"')";
connection = DriverManager.getConnection(url,username,password);
statement = connection.createStatement();
int i = statement.executeUpdate(sql);
if (i==1) {
System.out.println("插入数据成功");
}else {
System.out.println("插入数据失败");
}
connection.close();
statement.close();
return i;
}
//查询语句模块
public ResultSet selectAllData() throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.cj.jdbc.Driver");
String sql ="select * from student";
connection = DriverManager.getConnection(url,username,password);
statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String name = resultSet.getString("name");
String sno = resultSet.getString("sno");
String addr = resultSet.getString("addr");
String age = resultSet.getString("age");
System.out.println("姓名:"+name);
System.out.println("学号:"+sno);
System.out.println("住址:"+addr);
System.out.println("年龄:"+age);
System.out.println("----------------------------------------------------");
}
System.out.println("全部信息已显示完毕");
return null;
}
//更新语句模块
public int updateData(String sno,String age ) throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.cj.jdbc.Driver");
String sql ="update student set age="+age+" where sno='"+sno+"'";
connection = DriverManager.getConnection(url,username,password);
statement = connection.createStatement();
int i = 0 ;
i = statement.executeUpdate(sql);
if (i>=1) {
System.out.println("修改数据成功");
}else {
System.out.println("修改数据失败");
}
connection.close();
statement.close();
return i;
}
//删除语句模块
public int deleteData(String sno) throws SQLException, ClassNotFoundException{
int i = 0;
Class.forName("com.mysql.cj.jdbc.Driver");
String sql ="delete from student where sno='"+sno+"'";
connection = DriverManager.getConnection(url,username,password);
statement = connection.createStatement();
i = statement.executeUpdate(sql);
if (i==1) {
System.out.println("删除指定学号的学生信息成功");
}else {
System.out.println("删除指定学号的学生信息失败");
}
connection.close();
statement.close();
return i;
}
}