目录
JDBC介绍
JDBC实际上是一门实现java与 数据库 进行数据交互一门技术。
工作原理
jdbc连接
1导包
1在eclipse中创建lib文件夹，导入mysql第三方jar包（mysql-connector-java-5.144-bin.jar文件），构建开发环境（build path）
2获取连接四要素
// 1定义数据库连接四要素
String driverName = "com.mysql.jdbc.Driver";//告诉驱动管理者是哪个驱动
String uname = "root";//数据库用户名称
String upass = "123";//数据库用户密码
String url = "jdbc:mysql://localhost:3306/schooldb?characterEncoding=UTF-8";// 数据库地址dricerName获取：
1 在本项目中打开Referenced Libraries,找到com.mysql.jdbc包
2 找到Driver.class类
3复制权限和类名
4 粘贴权限和类名（com.mysql.jdbc.Driver）
数据库用户名、密码、端口号的获取
连接数据库
public static void main(String[] args) throws Exception {
// 1定义数据库连接四要素
String driverName = "com.mysql.jdbc.Driver";//告诉驱动管理者是哪个驱动
String uname = "root";//数据库用户名称
String upass = "123";//数据库用户密码
String url = "jdbc:mysql://localhost:3306/schooldb?characterEncoding=UTF-8";// 数据库地址
// 2注册驱动
Class.forName(driverName);
// 3通过驱动管理者得到指定的数据库连接对象
Connection con = DriverManager.getConnection(url, uname, upass);
System.out.println(con);
// 4定义sql语句
String sql = "select * from student";
// 5定义sql语句的载体 statement父类 preparedStatement载体
PreparedStatement pst =con.prepareStatement(sql);
//返回结果集
ResultSet rs=pst.executeQuery();
//7处理结果集
System.out.println("编号\t用户名\t\t学生密码\t年级编号");
while (rs.next()) {//依次获取结果集中的一行数据
int id=rs.getInt(1);
String stuNo=rs.getString(2);
String stuPass=rs.getString(3);
int gradId=rs.getInt(4);
System.out.println(id+"\t"+stuNo+"\t\t"+stuPass+"\t"+gradId);
}
//关闭资源
con.close();
}列1：jdbc的优化
mysql代码
CREATE DATABASE SchoolDB;
USE SchoolDB;
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
stuNo VARCHAR(30) NOT NULL UNIQUE,
stuPass VARCHAR(30) DEFAULT '1234' NOT NULL,
gradeId INT
);
-- 删除表格
DROP TABLE student;
CREATE TABLE score(
id INT AUTO_INCREMENT PRIMARY KEY, -- 成绩表编号
cName VARCHAR(20) NOT NULL,-- 课程名称
score DOUBLE ,-- 分数
stuId INT -- 学生编号
);
CREATE TABLE grade(
id INT AUTO_INCREMENT PRIMARY KEY,
gradeName VARCHAR(20)
);
-- 建立 grade--student主外键关系
ALTER TABLE student -- 外键表
ADD CONSTRAINT stu_Fk1 -- 外键约束名称
FOREIGN KEY(gradeId) -- 外键对应的字段
REFERENCES grade(id); -- 对应主键表的主键
-- 建立 student---score 主外键关系
ALTER TABLE score
ADD CONSTRAINT score_Fk1
FOREIGN KEY(stuId)
REFERENCES student(id);
-- 1.删除数据的时候 先插入主键表数据 在插入外键表数据
-- 1.插入年级表
INSERT INTO grade(gradeName) VALUES('大一');
INSERT INTO grade(gradeName) VALUES('大二');
INSERT INTO grade(gradeName) VALUES('大三');
INSERT INTO grade(gradeName) VALUES('大四');
SELECT* FROM grade;
-- 2.学生表插入数据
INSERT INTO student(stuNo,gradeId)
VALUES('jack',4);
INSERT INTO student(stuNo,gradeId)
VALUES('frank',4);
INSERT INTO student(stuNo,gradeId)
VALUES('mary',3);
INSERT INTO student(stuNo,gradeId)
VALUES('rouse',3);
INSERT INTO student(stuNo,gradeId)
VALUES('ooook',3);
INSERT INTO student(stuNo,gradeId)
VALUES('koooo',1);
INSERT INTO student(stuNo,gradeId)
VALUES('quekaozhe',1);
SELECT * FROM student;
-- 3.插入成绩表
INSERT INTO score(stuId,cName,score)
VALUES(1,'java程序设计',89);
INSERT INTO score(stuId,cName,score)
VALUES(1,'mysql数据库设计',89);
INSERT INTO score(stuId,cName,score)
VALUES(1,'html网页设计',89);
INSERT INTO score(stuId,cName,score)
VALUES(2,'java程序设计',89);
INSERT INTO score(stuId,cName,score)
VALUES(3,'java程序设计',89);
INSERT INTO score(stuId,cName,score)
VALUES(3,'c#程序设计',89);
INSERT INTO score(stuId,cName,score)
VALUES(3,'数据结构',89);
INSERT INTO score(stuId,cName,score)
VALUES(4,'数据结构',89);
INSERT INTO score(stuId,cName,score)
VALUES(5,'数据结构',89);
INSERT INTO score(stuId,cName,score)
VALUES(6,'数据结构',89);import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ConnectionUtil {
private static Connection con;
private static PreparedStatement pst;
private static ResultSet rs;
//获取连接工具类
public static Connection getConnection() throws ClassNotFoundException, SQLException {
// 1定义数据库连接四要素
String driverName = "com.mysql.jdbc.Driver";
String uname = "root";
String upass = "123";
String url = "jdbc:mysql://localhost:3306/schooldb?characterEncoding=UTF-8";// 数据库地址
Class.forName(driverName);// 注册驱动
con = DriverManager.getConnection(url);// 获取驱动连接
return con;
}
//关闭资源的方法
static public void close(Connection con, PreparedStatement pst, ResultSet rs) throws Exception {
if (rs != null) {
rs.close();
} else if (pst != null) {
pst.close();
} else if (con != null) {
con.close();
}
}
}import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) throws SQLException, Exception {
query();
// add("1", "2", 3);
getStuById(3);
update(2, "2");
query();
}
//1.查询
public static void query() throws Exception, SQLException {
String sql = "select * from student";
Connection con = ConnectionUtil.getConnection();// 获取驱动连接
PreparedStatement pst = null;
ResultSet rs = null;
try {
pst = con.prepareStatement(sql);// 预编译的 SQL 语句的对象
// executeQuery():查询
rs = pst.executeQuery();
System.out.println("编号\t用户名\t学生密码\t年级编号");
while (rs.next()) {// 依次获取结果集中的一行数据
int id = rs.getInt(1);// 第一列
String stuNo = rs.getString(2);
String stuPass = rs.getString(3);
int gradeId = rs.getInt(4);
System.out.println(id + "\t" + stuNo + "\t" + stuPass + "\t" + gradeId);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionUtil.close(con, pst, rs);
}
}
//根据id查询个人信息
public static void getStuById(int id) throws Exception {
String sql = "select * from student where id=?";
Connection con = ConnectionUtil.getConnection();
PreparedStatement pst = null;
ResultSet rs = null;
try {
pst = con.prepareStatement(sql);
pst.setInt(1, id);
// executeQuery():查询
rs = pst.executeQuery();
System.out.println("编号\t用户名\t学生密码\t年级编号");
while (rs.next()) {// 依次获取结果集中的一行数据
int id1 = rs.getInt(1);// 第一列
String stuNo = rs.getString(2);
String stuPass = rs.getString(3);
int gradeId = rs.getInt(4);
System.out.println(id1 + "\t" + stuNo + "\t" + stuPass + "\t" + gradeId);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionUtil.close(con, pst, rs);
}
}
//2.添加数据
public static void add(String stuNo, String stuPass, int gradeId) throws Exception, SQLException {
// 占位符号(定义了3个参数)
String sql = "insert into Student(stuNo,stuPass,gradeId) values(?,?,?)";
Connection con = ConnectionUtil.getConnection();
PreparedStatement pst = null;
pst = con.prepareStatement(sql);
// 用数据依次替换占位符号
pst.setString(1, stuNo);
pst.setString(2, stuPass);
pst.setInt(3, gradeId);
// 可以将sql传递至数据库执行了executeUpdate():添加/删除/修改
int rows = pst.executeUpdate();
if (rows > 0) {
System.out.println("插入成功!!!");
} else {
System.out.println("插入失败!!!");
}
ConnectionUtil.close(con, pst, null);
}
/**
* 修改数据库中的数据
*
* @param id
* @param newPass
* @throws Exception
* @throws SQLException
*/
public static void update(int id, String newPass) throws Exception, SQLException {
String sql = "update student set stuPass=? where id=?";
Connection con = ConnectionUtil.getConnection();
PreparedStatement pst = null;
try {
pst = con.prepareStatement(sql);// 预编译的 SQL 语句的对象
pst.setString(1, newPass);
pst.setInt(2, id);
int rows = pst.executeUpdate();
if (rows > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionUtil.close(con, pst, null);
}
}
/**
* 删除数据
*
* @param id 根据id删除数据
* @throws Exception
*/
public static void del(int id) throws Exception {
String sql = "delete from student where id=?";
Connection con = ConnectionUtil.getConnection();
PreparedStatement pst = null;
try {
pst = con.prepareStatement(sql);
pst.setInt(1, id);
int rows = pst.executeUpdate();
if (rows > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionUtil.close(con, pst, null);
}
}
}列2：在项目中关于jdbc的优化，封装jdbc工具类
1 mysql代码用上面的代码
2 eclipse代码
创建properties文件
package com.jdbc.util中代码
创建属性文件 jdbc.properties:
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url= jdbc:mysql://localhost:3306/schooldb?characterEncoding=UTF-8
jdbc.user=root
jdbc.password=123创建接口
import java.sql.PreparedStatement;
public interface PreparedStatementSetter {
// 定义专们设置占位符的方法
public void setValue(PreparedStatement pst);
}
import java.sql.ResultSet;
public interface ResultSetter {
// 定义处理所有结果集的方法
public void doValue(ResultSet rs);
}import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCTemplate {
// 定义连接四要素
private static String driver;
private static String url;
private static String user;
private static String password;
// 定义jdbc-api
private static Connection con;
private static PreparedStatement pst;// 声明预编译的sql语句对象
private static ResultSet rs;
// 类加载的时候就会加载静态代码块
static {
String path = "src/com/jdbc/demo3/jdbc.properties";
try {
InputStream ins = new FileInputStream(path);
// 属性文件对象 专门对.properties文件的数据进行 键值对方式的封装
Properties pp = new Properties();
pp.load(ins);
driver = pp.getProperty("jdbc.driver");
url = pp.getProperty("jdbc.url");
user = pp.getProperty("jdbc.user");
password = pp.getProperty("jdbc.password");
// System.out.println("驱动类名:"+driver);
// System.out.println("URL地址:"+url);
// System.out.println("用户名:"+user);
// System.out.println("密码:"+password);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取链接
*/
static public Connection getConnection() {
try {
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
/**
* 关闭资源
*/
public static void close(Connection con, PreparedStatement pst, ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* DML操作:删除/修改/添加统称为DML操作
*
* @return
*/
static public int DML(String sql, PreparedStatementSetter preparedStatementSetter) {
int rows = 0;
getConnection();// 1.获取连接
// 2.获取sql语句的载体
try {
pst = con.prepareStatement(sql);
// 确定有占位符 传了子类过来了
if (preparedStatementSetter != null) {
// 3.设置占位符号
preparedStatementSetter.setValue(pst);
}
rows = pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(con, pst, rs);
}
return rows;
}
// DQL操作:查询操作
static public void DQL(String sql, PreparedStatementSetter pstSetter, ResultSetter rsSetter) {
// 1.获取连接
getConnection();
// 2.获取sql语句的载体
try {
pst = con.prepareStatement(sql);
if (pstSetter != null) {
pstSetter.setValue(pst);
}
// 返回结果集
rs = pst.executeQuery();
if (rsSetter != null) {
// 交给其子类取处理结果集
rsSetter.doValue(rs);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(con, pst, rs);
}
}
}package com.jdbc.demo3.dao;中代码
实现增删改查
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.jdbc.demo2.ConnectionUtil;
import com.jdbc.util.JDBCTemplate;
import com.jdbc.util.PreparedStatementSetter;
import com.jdbc.util.ResultSetter;
public class StuDao {
//查询所有用户信息
public void query() {
String sql="SELECT * FROM USER WHERE user_type=3";
//创建匿名类
System.out.println("用户ID-用户名-用户密码-用户性别-用户电话-用户邮箱-用户余额");
JDBCTemplate.DQL(sql,null,new ResultSetter() {
@Override
public void doValue(ResultSet rs) {
// TODO Auto-generated method stub
try {
while(rs.next()) {
System.out.println(rs.getInt(1)+"-"+rs.getString(2)+"-"+rs.getString(3)+
"-"+rs.getString(4)+"-"+rs.getString(5)+"-"+rs.getString(6)+"-"+rs.getDouble(7));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
});
}
}
//查询用户 根据用户名
public int selUserByUserName(String uname) {
String sql="SELECT COUNT(*) FROM USER WHERE user_name=? AND user_type=3";
//创建匿名类
JDBCTemplate.DQL(sql,new PreparedStatementSetter() {
@Override
public void setValue(PreparedStatement pst) {
// TODO Auto-generated method stub
try {
pst.setString(1, uname);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
},new ResultSetter() {
@Override
public void doValue(ResultSet rs) {
// TODO Auto-generated method stub
try {
while(rs.next()) {
nameflage=rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
});
return nameflage;
}
public void add(String name,String psw,String sex,String phone,String email,double money) {
String sql="insert into user(user_name,user_password,user_sex,user_phone,user_email,user_money) values(?,?,?,?,?,?)";
int rows=JDBCTemplate.DML(sql, new PreparedStatementSetter() {
@Override
public void setValue(PreparedStatement pst) {
// TODO Auto-generated method stub
try {
pst.setString(1, name);
pst.setString(2, psw);
pst.setString(3, sex);
pst.setString(4, phone);
pst.setString(5, email);
pst.setDouble(6, money);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
});
if(rows>0) {
System.out.println("用户注册成功！");
}else {
System.out.println("用户注册失败！");
}
}
//删除用户
public int delUserByid(int userid) {
String sql="DELETE FROM USER WHERE user_id=? AND user_type=3";
int rowss=JDBCTemplate.DML(sql, new PreparedStatementSetter() {
@Override
public void setValue(PreparedStatement pst) {
// TODO Auto-generated method stub
try {
pst.setInt(1, userid);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
});
return rowss;
}
//修改管理员密码
public int updateAdminPsw(String newPsw) {
String sql="UPDATE USER SET user_password=? WHERE user_type=1";
int rows=JDBCTemplate.DML(sql, new PreparedStatementSetter() {
@Override
public void setValue(PreparedStatement pst) {
// TODO Auto-generated method stub
try {
pst.setString(1, newPsw);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
});
return rows;
}package com.jdbc.demo3.main;代码
import com.jdbc.demo3.dao.StuDao;
public class MainTest {
public static void main(String[] args) {
StuDao sd = new StuDao();
sd.query();
}
}
752
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
