标题## jdbc连接mysql数据库
1.首先在项目根目录创lLib文件夹,放入jdbc驱动程序,然后Add As Library
2.建包
bean包:专门放置属性类
dao包: 进行数据操作的类
util包: 放工具类
test包: 测试类
正式开始
1.在bean包中写实体类:
package com.hopu.bean;
public class Member {
private String name;
private String pwd;
private float score;
private int rank;
public Member() {
}
public Member(String name, String pwd, float score, int rank) {
this.name = name;
this.pwd = pwd;
this.score = score;
this.rank = rank;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public float getScore() {
return score;
}
public void setScore(float score) {
this.score = score;
}
public int getRank() {
return rank;
}
public void setRank(int rank) {
this.rank = rank;
}
@Override
public String toString() {
return "Member{" +
"name='" + name + '\'' +
", pwd='" + pwd + '\'' +
", score=" + score +
", rank=" + rank +
'}';
}
}
对应的mysql数据库
2.在util包中的MemberUtil类写了连接数据库和释放资源两个方法:
package com.hopu.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class MemberUtil {
public static String driver="com.mysql.cj.jdbc.Driver";
public static String url="jdbc:mysql://127.0.0.1:3306/shop?useSSL=false&serverTimezone=UTC";
public static String username="root";
public static String password="678";
public static Connection con=null;
public static Connection getCon(){
try{
Class.forName(driver);
con = DriverManager.getConnection(url, username, password);
}catch (Exception e){
e.printStackTrace();
}
return con;
}
public static void myClose(ResultSet rs, PreparedStatement pstm, Connection con){
try{
if(rs!=null){
rs.close();
}
if(pstm!=null){
pstm.close();
}
if(con!=null){
con.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
public static void myClose( PreparedStatement pstm,Connection con){
try{
if(pstm!=null){
pstm.close();
}
if(con!=null){
con.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
}
getCon()方法返回 Connection 对象,myClose()方法无返回值。
dao包里面写增删改查四种方法,其中sql语句一定要写对,也不要有多余的空格。
package com.hopu.dao;
import com.hopu.bean.Member;
import com.hopu.util.MemberUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
public class MemberDao {
private Connection con=null;
private PreparedStatement pstm=null;
private ResultSet rs=null;
private int n=0;
public int add(Member member){
try{
con = MemberUtil.getCon();
String sql="insert into member values(?,?,?,?)";
pstm = con.prepareStatement(sql);
pstm.setObject(1,member.getName());
pstm.setObject(2,member.getPwd());
pstm.setObject(3,member.getScore());
pstm.setObject(4,member.getRank());
n = pstm.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally {
MemberUtil.myClose(pstm,con);
}
return n;
}
public int delete(int rank){
try{
con = MemberUtil.getCon();
String sql="delete from member where `rank`=?";
pstm = con.prepareStatement(sql);
pstm.setObject(1,rank);
n = pstm.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally {
MemberUtil.myClose(pstm,con);
}
return n;
}
public int update(Member member){
try{
con = MemberUtil.getCon();
String sql="update member set name=?,pwd=?,score=? where `rank`=?";
pstm = con.prepareStatement(sql);
pstm.setObject(1,member.getName());
pstm.setObject(2,member.getPwd());
pstm.setObject(3,member.getScore());
pstm.setObject(4,member.getRank());
n = pstm.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally {
MemberUtil.myClose(pstm,con);
}
return n;
}
public ArrayList<Member> selectAll(){
ArrayList<Member> list = new ArrayList<>();
try{
con = MemberUtil.getCon();
String sql="select * from member";
pstm = con.prepareStatement(sql);
rs = pstm.executeQuery();
while(rs.next()){
Member member = new Member();
member.setName(rs.getString("name"));
member.setPwd(rs.getString("pwd"));
member.setScore(rs.getFloat("score"));
member.setRank(rs.getInt("rank"));
list.add(member);
}
}catch (Exception e){
e.printStackTrace();
}finally {
MemberUtil.myClose(rs,pstm,con);
}
return list;
}
public Member selectById(int rank){
Member member = new Member();
try{
con = MemberUtil.getCon();
String sql="select * from member where `rank`=?";
pstm = con.prepareStatement(sql);
pstm.setObject(1,rank);
rs = pstm.executeQuery();
if(rs.next()){
member.setName(rs.getString("name"));
member.setPwd(rs.getString("pwd"));
member.setScore(rs.getFloat("score"));
member.setRank(rs.getInt("rank"));
}
}catch (Exception e){
e.printStackTrace();
}finally {
MemberUtil.myClose(rs,pstm,con);
}
return member;
}
}
4.test测试类中 因为写一个小功能就要写一个类,写一个main方法,所以我们直接在需要的地方打出@Test,注意要自己打这个是没有提示的,然后按下Alt + Enter 选择导入JUnit4
package com.hopu.test;
import com.hopu.bean.Member;
import com.hopu.dao.MemberDao;
import org.junit.Test;
import java.util.ArrayList;
public class Test01 {
@Test
public void add(){
MemberDao memberDao = new MemberDao();
Member member = new Member();
member.setName("小五");
member.setPwd("456");
member.setScore(56);
member.setRank(2);
int n = memberDao.add(member);
if(n>0){
System.out.println("添加成功!");
}else{
System.out.println("添加失败!");
}
}
@Test
public void delete(){
MemberDao memberDao = new MemberDao();
int n = memberDao.delete(2);
if(n>0){
System.out.println("删除成功!");
}else{
System.out.println("删除失败!");
}
}
@Test
public void update(){
MemberDao memberDao = new MemberDao();
Member member = memberDao.selectById(2);
member.setName("花花");
int n = memberDao.update(member);
if(n>0){
System.out.println("修改成功!");
}else{
System.out.println("修改失败!");
}
}
@Test
public void selectAll(){
MemberDao memberDao = new MemberDao();
ArrayList<Member> members = memberDao.selectAll();
for (Member member : members) {
System.out.println(member);
}
}
}
mysql数据库的运行结果:
显示运行成功,数据库发生变化,删改查一样运行对应的方法。
删除:
修改:
全查:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)