1关:JDBC 查询数据
package step1;
import java.sql.*;
public class Query {
public static void queryDB() {
// 声明变量
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
// "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
String username = "root";
String password = "123123";
// 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";
try {
/********* Begin *********/
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.建立数据库连接
conn = DriverManager.getConnection(url, username, password);
// 3.定义sql
String sql = "select * from s" ;
// 4.建立statement对象
stat = conn.createStatement();
// 5.查询数据,输出结果
rs = stat.executeQuery(sql);
System.out.print("sno"+"\t");
System.out.print("sname"+"\t");
System.out.print("status"+"\t");
System.out.println("city");
while (rs.next()) {
System.out.print(rs.getString("sno")+"\t");
System.out.print(rs.getString("sname")+"\t");
System.out.print(rs.getString("status")+"\t");
System.out.println(rs.getString("city"));
}
/********* End *********/
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stat != null) {
stat.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
第2关:JDBC 插入数据
package step1;
import java.sql.*;
public class Insert {
public static void insertDB() {
// 声明变量
Connection conn = null;
Statement stat = null;
int counts = 0; // 用于存储执行SQL后受影响的行数
// "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
String username = "root";
String password = "123123";
// 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";
try {
/********* Begin *********/
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.建立数据库连接
conn = DriverManager.getConnection(url, username, password);
// 3.建立statement对象
stat = conn.createStatement();
// 4.定义SQL
String sql = "INSERT INTO s VALUES('S6','泰鑫',10,'十堰')";
// 5.执行SQL
counts = stat.executeUpdate(sql);
// 6.处理结果
if (counts > 0) {
System.out.println("插入成功!");
// 如果插入成功,查询表格的SQL语句
sql = "SELECT * FROM s";
} else {
System.out.println("插入失败!");
}
/********* End *********/
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (stat != null) {
stat.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
第3关:JDBC 更新数据
package step1;
import java.sql.*;
public class Update {
public static void updateDB() {
// 声明变量
Connection conn = null;
Statement stat = null;
// "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
String username = "root";
String password = "123123";
// 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";
try {
/********* Begin *********/
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.建立数据库连接
conn = DriverManager.getConnection(url, username, password);
// 3.定义SQL
String sql= "update s set sname = '东风',status=40,city='十堰' where sno = 'S5' ";
// 4.建立statement对象
stat = conn.createStatement();
// 5.执行SQL
int counts = stat.executeUpdate(sql);
// 6.处理结果
if(counts>0){
System.out.println("更新成功!");
}else{
}
/********* End *********/
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (stat != null) {
stat.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
第4关:JDBC 删除数据
package step1;
import java.sql.*;
public class Delete {
public static void deleteDB() {
// 声明变量
Connection conn = null;
Statement stat = null;
// "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
String username = "root";
String password = "123123";
// 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";
try {
/********* Begin *********/
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.建立数据库连接
conn = DriverManager.getConnection(url, username, password);
// 3.定义SQL
String sql = "delete from s where sno='s5'";
// 4.建立statement对象
stat = conn.createStatement();
// 5.执行SQL
int counts = stat.executeUpdate(sql);
// 6.处理结果
if(counts>0){
System.out.println("删除成功!");
}else{
}
/********* End *********/
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (stat != null) {
stat.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
第5关:JDBC 条件查询
package step1;
import java.sql.*;
import java.util.Scanner;
public class QuerySno {
public static void queryDB() {
// 接收供应商编号
Scanner scanner = new Scanner(System.in);
String sno = scanner.next();
// 声明变量
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
// "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
String username = "root";
String password = "123123";
// 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";
try {
/********* Begin *********/
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.建立数据库连接
conn = DriverManager.getConnection(url, username, password);
// 3.定义sql
String sql= "select * from s where sno=?";
// 4.建立PreparedStatement对象
PreparedStatement pstmt = conn.prepareStatement(sql);
// 5.注册PreparedStatement对象的参数
pstmt.setString(1,sno);
// 6.查询数据,输出结果
rs = pstmt.executeQuery();
System.out.print("sno"+"\t");
System.out.print("sname"+"\t");
System.out.print("status"+"\t");
System.out.println("city");
while (rs.next()) {
System.out.print(rs.getString("sno")+"\t");
System.out.print(rs.getString("sname")+"\t");
System.out.print(rs.getString("status")+"\t");
System.out.println(rs.getString("city"));
}
/********* End *********/
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (psmt != null) {
psmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
标签:stat,String,数据库,编程,System,接口,null,conn,out
From: https://www.cnblogs.com/whwh/p/18183464