第一篇博客,写的有些简单,回忆了以前学的jdbc增删改查,希望能够给大家有所帮助,如有不好的地方,欢迎大家随时指正。后期还会继续更新
工程结构
使用软件
eclipse
mysql
代码:
1.jdbc.properties
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/student
name=root
password="mysql对应密码"
2.实体类(pojo)
package com.qyn.pojo;
/*
* user的实体类,包括id,用户名,密码
* */
public class User {
private int id;
private String usernaem;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsernaem() {
return usernaem;
}
public void setUsernaem(String usernaem) {
this.usernaem = usernaem;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public User() {}
public User(int id, String usernaem, String password) {
this.id = id;
this.usernaem = usernaem;
this.password = password;
}
public String toString() {
return "User [id=" + id + ", usernaem=" + usernaem + ", password=" + password + "]";
}
}
3.工具类(Dbutil)
package com.qyn.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class Dbutil {
//static关键字修饰的变量供所有实例共享
static String driverClass = null;
static String url = null;
static String name = null;
static String password = null;
static {
try{
//创建属性配置对象
Properties pro = new Properties();
//使用类加载器,去读取src底下的资源文件。 后面在servlet
InputStream is = new FileInputStream("jdbc.properties");
//导入输入流
pro.load(is);
//读取属性
driverClass = pro.getProperty("driverClass");
url = pro.getProperty("url");
name = pro.getProperty("name");
password = pro.getProperty("password");
}catch (Exception e) {
e.printStackTrace();
}
}
//获取连接对象
public static Connection getCon() {
Connection con = null;
try {
//加载驱动程序
Class.forName(driverClass );
//建立连接
con = DriverManager.getConnection(url,name,password);
} catch (Exception e) {
e.printStackTrace();
}
return con ;
}
//这样做提高了代码的复用性 是程序变得更加灵活
public static void closeAll(ResultSet rs,Statement st,Connection con) {
rsClose(rs);
stClose(st);
conClose(con);
}
public static void closeAll(Statement st,Connection con) {
stClose(st);
conClose(con);
}
public static void rsClose(ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void stClose(Statement st) {
try {
if(st != null) {
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void conClose(Connection con) {
try {
if(con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
dao层
package com.qyn.dao;
/*
* User类的增删改查
* */
import java.util.List;
import com.qyn.pojo.User;
public interface UserDao {
/*
* 更新数据
*/
int update(User u);
/*
* 删除数据
*/
int delete(int id);
/*
* 插入数据
*/
int insert(User u);
/*
* 查询所有的数据
*/
List<User> findAll();
/*
*按id查找数据
*/
List<User> findById(int id);
}
dao的实现类
package com.qyn.dao.impl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.mysql.jdbc.Connection;
import com.qyn.dao.UserDao;
import com.qyn.pojo.User;
import com.qyn.util.Dbutil;
public class UserDaoImpl implements UserDao{
int r=0;
@Override
public int update(User u) {
Connection con = null;
PreparedStatement ps = null;
try {
//建立连接
con=(Connection) Dbutil.getCon();
//创建prepaerdStatement对象
String sql="update user set username=?,password=? where id=?";
ps=con.prepareStatement(sql);
ps.setString(1, u.getUsernaem());
ps.setString(2, u.getPassword());
ps.setInt(3, u.getId());
r=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
Dbutil.closeAll(ps, con);
}
return r;
}
@Override
public int delete(int id) {
Connection con = null;
PreparedStatement ps = null;
try {
//建立连接
con=(Connection) Dbutil.getCon();
//创建prepaerdStatement对象
String sql="delete from user where id=?";
ps=con.prepareStatement(sql);
ps.setInt(1,id);
r=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
Dbutil.closeAll(ps, con);
}
return r;
}
@Override
public int insert(User u) {
Connection con = null;
PreparedStatement ps = null;
try {
//建立连接
con=(Connection) Dbutil.getCon();
//创建prepaerdStatement对象
String sql="insert into user values(null,?,?)";
ps=con.prepareStatement(sql);
ps.setString(1, u.getUsernaem());
ps.setString(2, u.getPassword());
r=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
Dbutil.closeAll(ps, con);
}
return r;
}
@Override
public List<User> findAll() {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<User> list =new ArrayList<User>();
try {
//建立连接
con=(Connection) Dbutil.getCon();
//创建prepaerdStatement对象
String sql="select * from user";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
User u = null;
while(rs.next()){
int id = rs.getInt(1);
String userName = rs.getString(2);
String passWord = rs.getString(3);
u = new User(id,userName,passWord);
list.add(u);
//System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
Dbutil.closeAll(rs, ps, con);
}
return list;
}
@Override
public List<User> findById(int id) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<User> list = new ArrayList<User>();
try {
//建立连接
con=(Connection) Dbutil.getCon();
//创建prepaerdStatement对象
String sql="select * from user where id = ?";
ps=con.prepareStatement(sql);
ps.setInt(1, id);
rs=ps.executeQuery();
User u =null;
while(rs.next()){
int Id =rs.getInt(1);
String userName = rs.getString(2);
String passWord = rs.getString(3);
u = new User(Id,userName,passWord);
list.add(u);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
Dbutil.closeAll(rs, ps, con);
}
return list;
}
}
service层
package com.qyn.service;
import java.util.ArrayList;
import java.util.List;
import com.qyn.dao.UserDao;
import com.qyn.dao.impl.UserDaoImpl;
import com.qyn.pojo.User;
public class UserDaoService {
UserDao udao = new UserDaoImpl();
int r= 0;
public int update(User u ){
r=udao.update(u);
if(r>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
return 0;
}
public int delete(int id){
r=udao.delete(id);
if(r>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
return 0;
}
public int insert(User u){
r=udao.insert(u);
if(r>0){
System.out.println("插入成功");
}else{
System.out.println("插入失败");
}
return 0;
}
public List<User> findById(int id){
List<User> list =new ArrayList<User>();
list = udao.findById(id);
System.out.println(list);
return list;
}
public List<User> findAll(){
List<User> list =new ArrayList<User>();
list = udao.findAll();
System.out.println(list);
return list;
}
}
测试类
package com.qyn.test;
import org.junit.Test;
import com.qyn.service.UserDaoService;
public class TestUser {
/*@Test
public void insert() {
User user = new User(1,"lisi","123");
UserDaoService uService =new UserDaoService();
uService.insert(user);
}*/
/*@Test
public void delete() {
UserDaoService uService =new UserDaoService();
uService.delete(2);
}
*/
/*@Test
public void update() {
UserDaoService uService =new UserDaoService();
User u = new User(3,"李四","123");
uService.update(u);
}*/
/*@Test
public void findAll() {
UserDaoService uService =new UserDaoService();
uService.findAll();
}*/
@Test
public void findById() {
UserDaoService uService =new UserDaoService();
uService.findById(4);
}
}
数据库表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;