目录
目录
一·开头
首先我们用idea创建项目,再通过官网下载相应的jar包驱动https://dev.mysql.com/doc/index-connectors.html 导入驱动jar包 下面我还提供了一个mysql-connector-java-5.1.48.jar放在网盘里面,需要的小伙伴可以进行下载
链接:https://pan.baidu.com/s/1jU2e53iZcKvSwXwjEHD05g?pwd=9GyE
提取码:9GyE在项目中创建一个lib文件夹,把刚刚下载的驱动文件复制过去。记住还要Add as Library
将它设置为模块有效,这是非常重要的
二·介绍
1.1 JDBC的概念
JDBC是java Database connetion(java数据库连接)的简写,是使用java语言操作数据库的一套API,由一组用java语言编写的接口和类组成,是java程序访问数据库的标准规范。
1.2JDBC的常用API
(1).DriverManager:(驱动管理类)
1.注册驱动
注意:Mysql5之后的驱动jar包,可以省略注册驱动的步骤
自动加载jar包中META-INF/services/Java.sql.Driver文件中的驱动类
2.获取数据库连接
url:连接路径
乱码处理:
如果数据库出现乱码,可以指定参数:?characterEncoding=utf8
SSL连接:
?useSSL=false
(2)connection接口:编程时使用该类对象来创建Statement对象。
(3)Statement接口:使用该类对象来执行sql语句
(4)PreparedStatement:Statement的子接口,它表示一条编译过的SQL语句
(4)ResultSet类:保存Statement执行后产生的结果
三·连接Mysql
所有代码:
public class JDBC {
public static void main(String[] args) throws Exception {
//1.加载并注册数据库驱动
//Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
String username = "root";
String password = "123456";
//2.获取数据库连接
Connection conn = DriverManager.getConnection(url, username, password);
//3.书写sql语句
String sql = "select * from account where id = ?";
//4.获取执行者对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.设置参数
pstmt.setString(1,"2");
//6.执行sql语句
ResultSet rs = pstmt.executeQuery();
//7.处理结果集
while (rs.next()){
int id = rs.getInt(1);
String userName = rs.getString(2);
String passWord = rs.getString(3);
System.out.println(id+"\t"+userName+"\t"+passWord);
}
//8.释放\
rs.close();
pstmt.close();
conn.close();
}
}
四·JDBC实战操作
创建一个dao类包,新建一个StudentDao类,在里面对学生的信息进行增删改查
Student类
public class Student {
private String sno; //学号
private String sname; //学生姓名
private String spassword;//学生密码
public Student(String sno, String sname, String spassword) {
this.sno = sno;
this.sname = sname;
}
public String getSno() {
return sno;
}
1.创建JDBC工具类JDBCUtiils
public class JDBCUtiils {
/**
*获取连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
Properties prop = new Properties();
prop.load(JDBCUtiils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
String driverClassName = prop.getProperty("driverClassName");
String url = prop.getProperty("url");
String username = prop.getProperty("username");
String password = prop.getProperty("password");
//加载驱动
Class.forName(driverClassName);
//获取连接
return DriverManager.getConnection(url, username, password);
}
/**
* 关闭连接
* @param conn
* @param pstmt
*/
public static void close(Connection conn, PreparedStatement pstmt){
if (pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
2.添加学生信息
public boolean addstudent(Student student) throws Exception {
boolean flag=false;
Connection conn = JDBCUtiils.getConnection();
String sql="INSERT into student VALUES(?,?,?)";
//获取执行sql对象
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,student.getSno());
pstmt.setString(2,student.getSname());
pstmt.setString(3,student.getSpassword());
flag=true;
int i = pstmt.executeUpdate();
if (i>0){
System.out.println("添加成功!");
}else {
System.out.println("添加失败!");
}
JDBCUtiils.close(conn,pstmt);
return flag;
}
3.删除学生信息
public boolean dropstudent(Student student) throws Exception {
boolean flag=false;
String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
String username="root";
String password="123456";
Connection conn = JDBCUtiils.getConnection();
String sql="DELETE FROM student WHERE sno=?";
//获取执行sql对象
PreparedStatement pstmt = conn.prepareStatement(sql);
flag=true;
int i = pstmt.executeUpdate();
if (i>0){
System.out.println("删除成功!");
}else {
System.out.println("删除失败!");
}
JDBCUtiils.close(conn,pstmt);
return flag;
}
4.修改学生信息
public boolean updatestudent(Student student) throws SQLException {
boolean flag=false;
String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
String username="root";
String password="123456";
try {
Connection conn = JDBCUtiils.getConnection();
String sql="UPDATE student SET sname=? WHERE sno=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,student.getSname());
int i = pstmt.executeUpdate();
if (i>0){
System.out.println("修改成功!");
flag=true;
}else {
System.out.println("修改失败!");
}
JDBCUtiils.close(conn,pstmt);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
return flag;
}
}
5.查询单个学生信息
//查询单个学生
public boolean selectstudent(Student student) throws SQLException {
boolean flag=false;
String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
String username="root";
String password="123456";
Connection conn = DriverManager.getConnection(url,username,password);
String sql="select * from student where sno=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,student.getSno());
ResultSet rs = pstmt.executeQuery();
System.out.println("学号"+"\t"+"姓名"+"\t"+"密码"+"\t");
while (rs.next()){
String sno = rs.getString(1);
String uname = rs.getString(2);
String upassword = rs.getString(3);
System.out.println(sno+"\t"+uname+"\t"+upassword+"\t");
}
pstmt.close();
conn.close();
return flag;
}
6.查询所有学生信息
//查询所有学生
public boolean selectallstudent() throws SQLException {
boolean flag=false;
String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
String username="root";
String password="123456";
Connection conn = DriverManager.getConnection(url,username,password);
String sql="select * from student";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
System.out.println("学号"+"\t"+"姓名"+"\t"+"密码"+"\t");
while (rs.next()){
String sno = rs.getString(1);
String uname = rs.getString(2);
String upassword = rs.getString(3);
System.out.println(sno+"\t"+uname+"\t"+upassword+"\t");
}
pstmt.close();
conn.close();
return flag;
}