Jdbc----原生7大步骤:
1)导包并且注册驱动
Class.forName("com.mysql.jdbc.Driver") ;
//mysql8.0的jar包 "com.mysql.cj.jdbc.Driver"
2)获取数据库的连接对象
Connection conn = DriverManager.getConnection(
url:"jdbc:mysql://localhost:3306/库名",
username:"root",
password:"123456")
//注意:mysql8.0以上的包:"jdbc:mysql://localhost:3306/库名?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true"
3)准备好的sql语句
String sql = "insert into account(name,balance) values('高圆圆',1000)" ;
4)获取数据库的执行对象---执行sql语句
Statement stmt = conn.createStatement();
5)数据库的执行对象 去操作数据库
int count = stmt.executeUpdate(sql);
6)返回结果
System.out.println("影响了"+count+"行") ;
7)释放资源
stmt.close();
conn.close();
2,使用Statement对象完成DDL语句的操作:
//1)导包,注册驱动
Class.forName("com.mysql.jdbc.Driver") ;
//2)获取数据库连接对象
Connection connection =DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myee_2203_02",
"root",
"123456");
//3)准备sql语句
String sql = "CREATE TABLE student("+
" id INT PRIMARY KEY AUTO_INCREMENT," +
" NAME VARCHAR(10), " +
" gender VARCHAR(2), " +
" address VARCHAR(50), " +
" hobby VARCHAR(100) " +
") " ;
//4)使用连接对象创建执行对象
Statement stmt = connection.createStatement();
//5)执行sql,发送数据库
int count = stmt.executeUpdate(sql);
System.out.println("影响"+count+"行") ;
//6)释放资源
stmt.close();
connection.close();
3,使用Statement对象完成DML语句的操作:
//1)注册驱动
Class.forName("com.mysql.jdbc.Driver") ;
//2)获取数据库连接对象
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myee_2203_02",
"root",
"123456") ;
//3)准备sql语句
String sql = "insert into
student(name,gender,address,hobby)
values('张佳宁','女','鄠邑区','演戏')," +
"('刘翔','男','西安市','跑步')," +
"('小虎队','男','西安市','唱歌');" ;
String sql = "update student set name = '周杰伦' where id = 3" ;
String sql = "delete from student where id = 3" ;
//4)连接对象获取执行对象
Statement stmt = conn.createStatement();
//5)执行sql,发送数据库
int count = stmt.executeUpdate(sql) ;
System.out.println("影响了"+count+"行") ;
//6)释放资源
stmt.close();
conn.close();
4,使用Statement对象完成DQL语句的操作:
//1)注册驱动
Class.forName("com.mysql.jdbc.Driver") ;
//2)获取数据库的连接对象
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myee_2203_02",
"root",
"123456") ;
//3)准备sql语句
String sql ="select * from student" ;
//4)获取执行对象
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
System.out.println("学生列表如下:");
System.out.println("编号\t姓名\t性别\t地址\t爱好");
//第一次获取
if(rs.next()){
int id = rs.getInt(1); //获取编号
String name = rs.getString(2); //获取姓名
String gender = rs.getString(3); //获取性别
String addrses = rs.getString(4); //获取地址
String hobby = rs.getString(5);//获取爱好
System.out.println(id+"\t\t"+name+"\t"+gender+"\t\t"+addrses+"\t"+hobby);
}
//第二次获取
if(rs.next()){
int id = rs.getInt("id"); //获取编号
String name = rs.getString("name"); //获取姓名
String gender = rs.getString("gender"); //获取性别
String addrses = rs.getString("address"); //获取地址
String hobby = rs.getString("hobby");//获取爱好
System.out.println(id+"\t\t"+name+"\t"+gender+"\t\t"+addrses+"\t"+hobby);
}*/
//不明确循环次数
while(rs.next()){
int id = rs.getInt("id"); //获取编号
String name = rs.getString("name"); //获取姓名
String gender = rs.getString("gender"); //获取性别
String addrses = rs.getString("address"); //获取地址
String hobby = rs.getString("hobby");//获取爱好
System.out.println(id+"\t\t"+name+"\t"+gender+"\t\t"+addrses+"\t"+hobby);
}
//5)释放资源
rs.close();
stmt.close();
connection.close();
5,将jdac的部分操作封装到工具类中使用:
1)创建一个.properties文件,将工具类中的属性封装到此文件中:
eg:
driverClassname=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myee_2203_02
username=root
password=123456
2)封装步骤:
public class JdbcUtils {
//成员变量声明
private static String url = null;
private static String username = null;
private static String password = null;
private static String driverClassname = null;
//try--catch-->ctrl+alt+t---->选择 6
//静态代码块
static {
try {
//应该要获取url,username,password,driverClassname 这几个值
//需要properties配置文件
//java.util.Properties:属性集合列表 :属性列表中的每个键及其对应的值都是一个字符串。
Properties prop = new Properties();
//有个功能:public void load(InputStream inStream):将输入流中的内容到属性集合列表中
//读取src下面的jdbc.properties配置文件
//获取资源文件所在的输入流对象
InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
//加载流对象到属性集合列表中
prop.load(inputStream);
//java.util.Properties的特有功能:可以通过键获取值
//public String getProperty(String key)
driverClassname = prop.getProperty("driverClassname"); //获取驱动类
url = prop.getProperty("url"); //获取url
username = prop.getProperty("username"); //用户名
password = prop.getProperty("password");//密码
//注册驱动
Class.forName(driverClassname);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//防止获取工具类对象
private JdbcUtils() {
}
//获取连接对象
public static Connection getConnnection() {
try {
Connection connection = DriverManager.getConnection(
url,
username,
password
);
return connection;
} catch (SQLException e) {
e.printStackTrace();
}
return null ;
}
//释放资源
//如果是针对ddl语句或者dml语句,Statement对象以及Connection对象关闭
public static void close(Statement stmt, Connection conn) {
close(null, stmt, conn);
}
//如果是针对DQL语句,ResultSet对象以及Statement以及Connection对象关闭
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
6,将数据库中的数据封装到List集合中:
eg:
学生类:
public class Student {
private int id ;//编号
private String name ; //姓名
private String gender ;//性别
private String address ;//地址
private String hobby ;//爱好
//alt+ins键 --->选择 constructor--->选择 select none 无参构造方法
public Student() {
}
//alt+ins键 --->选择 constructor--->选中的所有属性
public Student(int id, String name, String gender, String address, String hobby) {
this.id = id;
this.name = name;
this.gender = gender;
this.address = address;
this.hobby = hobby;
}
//alt+ins键 (部分电脑需要按fn) ---->getter and setter
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
//alt+ins键--->选择---toString() --防止打印出来地址值
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", address='" + address + '\'' +
", hobby='" + hobby + '\'' +
'}';
}
}
封装:
public class Test {
public static void main(String[] args) throws Exception {
//调用
List<Student> list = findAllStudent();
if(list!=null){
for(Student s:list){
System.out.println(s) ;//执行toString()方法
System.out.println(s.getId()+"----"+s.getName()+"---"+s.getGender()+"---"+s.getAddress()+"---"+s.getHobby());
}
}
}
//定义一个功能
public static List<Student> findAllStudent() throws Exception {
//1)创建一个List集合
List<Student> list = new ArrayList<>() ;
//2)JDBC操作,获取数据库的连接对象
Connection conn = JdbcUtils.getConnnection();
//3)准备sql
String sql = "select * from student" ;
//4)获取执行对象并且同时执行查询操作
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
//声明一个Student类型变量s
Student s = null ;
while(rs.next()){//判断是否有下一条数据
//只要下一条数据,就是一个学生对象
s = new Student() ;
//封装数据
//int id = rs.getInt("id"); //获取id值
s.setId(rs.getInt("id"));
s.setName(rs.getString("name"));
s.setGender(rs.getString("gender"));
s.setAddress(rs.getString("address"));
s.setHobby(rs.getString("hobby"));
//还需要将s 这些对象添加到集合中
list.add(s) ;
}
return list ;
}