目录
环境配置
代码
运行结果
环境配置
首先IDE我用的是Eclipse。
数据库驱动用的(mysql-connector-java-8.0.29)。
驱动下载地址
java项目使用驱动的过程是如下图
1.点击工具栏的project
2.点击project栏目里的Properties
3.点击Java Build Path进入如下界面
4.点击 Add External JARs 进入如下界面
5.选择你下载的驱动文件(.jar)
6.出现如下界面代表此项目引用mysql驱动成功
代码
书籍信息表如下：
字段是：id,name,author,company,price
意思是：书号，书名，作者，出版社，价格
package test_jdbc;
import java.sql.*;
import java.util.Scanner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class connect {
//添加驱动
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//说明以下字符串含义
//jdbc:mysql:port port即mysql服务的端口号，默认是3306，如果不是需加上端口号
//localhost:数据库所在ip(127.0.0.1)
//test :链接的数据库名
static final String DB_URL = "jdbc:mysql://localhost/test";
// 数据库名和密码根据自身情况修改修改
static final String USER = "root";
static final String PASS = "1234";
public static void main(String[] args)
{
Scanner scan = new Scanner(System.in);
System.out.println("请输入用户名：");
String user_id = scan.next();
System.out.println("\n请输入密码：");
String password = scan.next();
//通过person 表来通过mysql实现验证登录
//person 字段为 id,password,level
// 用户名，密码，权限等级（1级为管理员权限）
//返回值为 权限等级，返回0 代表不存在此用户
int level = check_person(user_id,password);
if(level>0)
{
while(true)
{
//主要功能如下
/*
* 普通用户只有查看功能
* 只有管理员才能修改、删除、和添加
*/
System.out.println("\n1:查询\n"
+ "2:修改\n"
+ "3:删除\n"
+ "4:添加\n"
+ "5:退出");
System.out.println("请输入指令：");
int code = scan.nextInt();
if(code==1){
System.out.println("请输入相关信息：");
String str = scan.next();
select_from(str);
}
else if(code==2){
if(level==1)
{
System.out.println("输入书号：");
String id = scan.next();
System.out.println("输入修改字段(id,name,author,company,price)：");
String colname = scan.next();
if(colname.compareTo("price")==0)
{
System.out.println("输入修改后的值：");
String price = scan.next();
if(check_price(price) == true)
{
update(id, colname, price);
}
else
{
System.out.println("价格输入不符合规定，最多保留两位小数");
}
}
else
{
System.out.println("输入修改后的值：");
String price = scan.next();
update(id, colname, price);
}
}
else
{
System.out.println("权限不够");
}
}
else if(code==3)
{
if(level==1){
System.out.println("\n输入要删除的书的书号：");
String id = scan.next();
delete_from(id);
}
else
{
System.out.println("权限不够");
}
}
else if(code==4)
{
if(level==1){
System.out.println("输入书号：");
String id = scan.next();
System.out.println("输入书名：");
String name = scan.next();
System.out.println("输入作者：");
String author = scan.next();
System.out.println("输入出版社：");
String company = scan.next();
System.out.println("输入价格：");
String price = scan.next();
if(check_price(price)==true)
{
String[] str= {id,name,author,company,price};
insert_into(str);
}
else
{
System.out.println("价格输入不符合规定，最多保留两位小数");
}
}
else
{
System.out.println("权限不够");
}
}
else if(code==5)
{
break;
}
}
}
if(level==0){
System.out.println("用户名或密码错误");
}
else{
System.out.println("成功退出");
}
scan.close();
}
//校验登录,返回权限值，0代表登录失败
public static int check_person(String id,String pass){
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
stmt = conn.createStatement();
String sql;
sql = "select * from person where id='"+id+"' and password='"+pass+"'";
// id name author company price
ResultSet rs = stmt.executeQuery(sql);
int num=0;
int code=0;
while(rs.next())
{
code = rs.getInt(3);
num+=1;
}
if(num==0)
{
code=0;
}
rs.close();
stmt.close();
conn.close();
return code;
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
return 0;
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
return 0;
}
}
//利用正则表达式校验价格，最多保留两位小数
public static boolean check_price(String price){
Pattern price_check = Pattern.compile("\\d+\\.{0,1}\\d{0,2}");
return price_check.matcher(price).matches();
}
public static void insert_into(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
stmt = conn.createStatement();
String sql;
// id name author company price
sql = "insert into book values('"+args[0]+"','"+args[1]+"','"+args[2]+"','"+args[3]+"','"+args[4]+"')";
stmt.executeUpdate(sql);
stmt.close();
conn.close();
}catch(SQLException se){
System.out.println("插入失败");
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}
System.out.println("插入成功");
}
public static void select_from(String str) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
stmt = conn.createStatement();
String sql;
sql ="select * from book where id like '%"+str+"%' or name like '%"+str+"%' "
+ "or author like '%"+str+"%' or company like '%"+str+"%'";
ResultSet rs = stmt.executeQuery(sql);
int num=0;
System.out.println("书号\t书名\t作者\t出版社\t价格");
while(rs.next())
{
num+=1;
System.out.print(rs.getString(1)+"\t");
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getString(3)+"\t");
System.out.print(rs.getString(4)+"\t");
System.out.print(rs.getString(5)+"\n");
}
System.out.println("一共 "+num+" 条结果");
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}
}
public static void delete_from(String str) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
stmt = conn.createStatement();
String sql;
/* 模糊删除
sql ="select from book where id like '%"+str+"%' or name like '%"+str+"%' "
+ "or author like '%"+str+"%' or company like '%"+str+"%'";
*/
sql = "delete from book where id = '"+str+"'";
stmt.executeUpdate(sql);
stmt.close();
conn.close();
System.out.print("删除成功\n");
}catch(SQLException se){
//Handle errors for JDBC
System.out.print("删除失败\n");
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}
}
public static void update(String id,String colname,String str) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
stmt = conn.createStatement();
String sql;
sql = "update book set "+colname+" = '"+str+"' where id = '"+id+"'";
stmt.executeUpdate(sql);
System.out.println("修改成功");
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}
}
}
运行结果
person 表
图书表
运行截图
基本功能实现
以上仅为学习JDBC的一些记录
仅供参考