一、 连接数据库查询数据库中数据
String url="jdbc:mysql://localhost:3306/pet?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8";
String userName="root";
String password="123456";
1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
2.获取连接
Connection conn= DriverManager.getConnection(url,userName,password);
3.创建Starement
Statement statement=conn.createStatement();
4.执行sql语句,并用ResultSet接受查询结果
ResultSet rs=statement.executeQuery("select * from pet ");
5.查看查询结果
while (rs.next()){
int id=rs.getInt(1);
String name=rs.getString("name");
String type_name=rs.getString("type_name");
System.out.println("id:"+id+"\t name:"+name+"\ttype_name:"+type_name);
}
6.释放资源
rs.close();
statement.close();
conn.close();
二、连接数据库方法
//配置文件database.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/pet?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
user=root
password=123456
public class DBUtil {
static String driver;//数据库驱动
static String url;
static String user;//数据库用户名
static String password;//数据库密码
static {//静态代码块,在类加载的时候
init();
}
public static void init(){//初始化连接参数,从配置文件里获得
Properties params=new Properties();
String configFile="database.properties";//配置文件路径
InputStream is=DBUtil.class.getClassLoader().getResourceAsStream(configFile);//加载配置文件到输入流中
try {
params.load(is);//从输入流中读取属性列表
} catch (IOException e) {
e.printStackTrace();
}
//获取指定的属性名获取对应的值
driver=params.getProperty("driver");
url=params.getProperty("url");
user=params.getProperty("user");
password=params.getProperty("password");
}
public static Connection getConn(){
Connection conn=null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取连接
conn= DriverManager.getConnection(url,user,password);
if(conn!=null){
System.out.println("数据库连接成功");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn;
}
//关闭资源
public static void classAll(Connection conn, Statement statement, ResultSet rs){
try {
if(rs!=null){
rs.close();
}
if(statement!=null){
statement.close();
}
if(conn!=null){
conn.close();
}
}catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
二、JDBC实现增删改查
1.数据库表
id int //主键 自动递增
name varchar
age int
bir date
phone varchar
role int
2.创建构造方法
放到bean包
public class Userinfo {
private int id;
private String name;
private String password;
private int age;
private String bir;
private String phone;
public Userinfo() {
}
public Userinfo(int id, String name, String password, int age, String bir, String phone) {
this.id = id;
this.name = name;
this.password = password;
this.age = age;
this.bir = bir;
this.phone = phone;
}
public Userinfo(String name, String password, int age, String bir, String phone) {
this.id = id;
this.name = name;
this.password = password;
this.age = age;
this.bir = bir;
this.phone = phone;
}
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getBir() {
return bir;
}
public void setBir(String bir) {
this.bir = bir;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
2.提供接口实现接口
接口放到dao包中
//接口
public interface UserinfoDao {
//登录
Userinfo getUserByPwd(String name,String password);
//添加用户信息
boolean inserUser(Userinfo userinfo);
//删除用户信息
boolean deleteUser(int id);
//修改用户信息
boolean updateUser(Userinfo userinfo);
//查询用户列表
List<Userinfo> getUserList();
//根据id查询单个用户
Userinfo getUserById(int id);
}
实现接口
在dao.impl包中
public class UserinfoDaoImpl implements com.dao.UserinfoDao {
@Override
public Userinfo getUserByPwd(String name, String password) {
Userinfo userinfo=null;
Connection conn=null;
PreparedStatement pStatement=null;
ResultSet rs=null;
try {
conn= DBUtil.getConn();
String sql="select * from userinfo where name=? and password=?;";
pStatement=conn.prepareStatement(sql);
pStatement.setString(1,name);
pStatement.setString(2,password);
rs=pStatement.executeQuery();
if (rs.next()){
int id=rs.getInt("id");
int age=rs.getInt("age");
userinfo=new Userinfo(id,name,password,age,rs.getString("bir"),rs.getString("phone"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DBUtil.classAll(conn,pStatement,rs);
}
return userinfo;
}
@Override
public boolean inserUser(Userinfo userinfo) {
boolean flag=false;
Connection conn=null;
PreparedStatement pStatement=null;
conn=DBUtil.getConn();
String sql="insert into userinfo(name,password,age,bir,phone)value (?,?,?,?,?)";
try {
pStatement=conn.prepareStatement(sql);
pStatement.setString(1,userinfo.getName());
pStatement.setString(2,userinfo.getPassword());
pStatement.setInt(3,userinfo.getAge());
pStatement.setString(4,userinfo.getBir());
pStatement.setString(5,userinfo.getPhone());
int rowNum=pStatement.executeUpdate();
if (rowNum==1){
flag=true;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DBUtil.classAll(conn,pStatement,null);
}
return flag;
}
@Override
public boolean deleteUser(int id) {
boolean flag=false;
Connection conn=null;
PreparedStatement pStatement=null;
conn=DBUtil.getConn();
String sql="delete from userinfo where id=?;";
try {
pStatement=conn.prepareStatement(sql);
pStatement.setInt(1,id);
int rowNum=pStatement.executeUpdate();
if (rowNum==1){
flag=true;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DBUtil.classAll(conn,pStatement,null);
}
return flag;
}
@Override
public boolean updateUser(Userinfo userinfo) {
boolean flag=false;
Connection conn=null;
PreparedStatement pStatement=null;
conn=DBUtil.getConn();
String sql="UPDATE userinfo SET name=?,password=?,age=?,bir=?,phone=? where id=?";
try {
pStatement=conn.prepareStatement(sql);
pStatement.setString(1,userinfo.getName());
pStatement.setString(2,userinfo.getPassword());
pStatement.setInt(3,userinfo.getAge());
pStatement.setString(4,userinfo.getBir());
pStatement.setString(5,userinfo.getPhone());
pStatement.setInt(6,userinfo.getId());
int rowNum=pStatement.executeUpdate();
if (rowNum==1){
flag=true;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DBUtil.classAll(conn,pStatement,null);
}
return flag;
}
@Override
public List<Userinfo> getUserList() {
List<Userinfo> userinfos=new ArrayList<Userinfo>();
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
try {
conn=DBUtil.getConn();
statement=conn.createStatement();
String sql="select * from userinfo ;";
rs=statement.executeQuery(sql);
while (rs.next()){
int id=rs.getInt("id");
int age=rs.getInt("age");
Userinfo userinfo=new Userinfo(id,rs.getString("name"),rs.getString("password"),age,rs.getString("bir"),rs.getString("phone"));
userinfos.add(userinfo);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DBUtil.classAll(conn,statement,rs);
}
return userinfos;
}
@Override
public Userinfo getUserById(int id) {
Userinfo userinfo=null;
Connection conn=null;
PreparedStatement pStatement=null;
ResultSet rs=null;
try {
conn= DBUtil.getConn();
String sql="select * from userinfo where id=? ;";
pStatement=conn.prepareStatement(sql);
pStatement.setInt(1,id);
rs=pStatement.executeQuery();
if (rs.next()){
int id1=rs.getInt("id");
int age=rs.getInt("age");
userinfo=new Userinfo(id,rs.getString("name"),rs.getString("password"),age,rs.getString("bir"),rs.getString("phone"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DBUtil.classAll(conn,pStatement,rs);
}
return userinfo;
}
}