使用JDBC连接数据库(一)(最普通的连接数据库方式)
一、JDBC连接数据库准备工作:
1.工程结构:
2.数据库建表及插入数据:
create table user(
id int auto_increment primary key,
username varchar(50),
password varchar(50)
);
insert into user(username,password) value("小明",123);
insert into user(username,password) value("小王",456);
二、JDBC连接数据库
a)实体类的代码演示:
package com.database.entity;
public class User {
private int id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + "]";
}
}
b)连接数据库代码实现:
package com.database.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DataBaseDao {
public static void main(String[] args) {
String url = "jdbc:mysql://127.0.0.1:3306/database?userUnicode=true&characterEncoding=UTF-8";
String username = "root";
String password = "";
try {
// 1.加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获得数据库连接
Connection conn = DriverManager.getConnection(url, username, password);
// 测试代码
System.out.println(conn);
// 3.关闭资源
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
三、常用操作(对于数据表进行增删改查操作)
1.增加操作(增加user表中数据)
代码实现:
public class DataBaseDao {
public static void main(String[] args) {
String url = "jdbc:mysql://127.0.0.1:3306/database?userUnicode=true&characterEncoding=UTF-8";
String username = "root";
String password = "";
try {
// 1.加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.通过DriverManager驱动程序管理类创建数据库连接
Connection conn = DriverManager.getConnection(url, username,password);
// 3.通过连接创建PrepareStatement类对象
String sql="insert into user (username,password) value('小红',123)";
PreparedStatement pst=conn.prepareStatement(sql);
// 4.完成
pst.execute();
// 5.关闭资源
pst.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2.删除操作(删除user表中数据)
代码实现:
package com.database.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DataBaseDao {
public static void main(String[] args) {
String url = "jdbc:mysql://127.0.0.1:3306/database?userUnicode=ture&characterEncoding=UTF-8";
String username = "root";
String password = "";
try {
// 1.加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.通过驱动管理类DriverManager创建数据库连接
Connection conn = DriverManager.getConnection(url, username,password);
// 3.通过连接创建PrepareStatement类对象
String sql = "delete from user where id=2";
PreparedStatement pst = conn.prepareStatement(sql);
// 4.完成
pst.execute();
// 5.关闭资源
pst.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
3.修改操作(修改user表中数据)
代码实现:
package com.database.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DataBaseDao {
public static void main(String[] args) {
String url = "jdbc:mysql://127.0.0.1:3306/database?userUnicode=true&characterEncoding=UTF-8";
String username = "root";
String password = "";
try {
// 1.加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.通过驱动管理类DriverManager创建连接
Connection conn = DriverManager.getConnection(url, username,password);
// 3.通过连接创建PrepareStatement类对象
String sql = "update user set username='测试修改' where id=2";
PreparedStatement pst = conn.prepareStatement(sql);
// 4.完成
pst.execute();
// 5.关闭资源
pst.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
4.查询操作(查询user表中数据)
代码实现:
package com.database.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DataBaseDao {
public static void main(String[] args) {
String url = "jdbc:mysql://127.0.0.1:3306/database?userUnicode=ture&characterEncoding=UTF-8";
String username = "root";
String password = "";
try {
// 1.加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.通过驱动管理类DriverManager创建数据库连接
Connection conn = DriverManager.getConnection(url, username,password);
// 3.通过连接创建Statement类对象
Statement st = conn.createStatement();
// 4.通过Statement对象操作数据库放入结果集
String sql = "select * from user";
ResultSet rs = st.executeQuery(sql);
// 5.遍历结果集
while (rs.next()) {
System.out.println(rs.getString("username") + "::"+ rs.getString("password"));
}
// 6.关闭资源
st.close();
rs.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO: handle exception
}
}
}