JDBC连接MYSQL,JDBC增删改查 经典 范例

阿使得肌肤·

　　一切详情请看代码：绝对经典，注视写的很清楚
　　

package cn.csdn.dao;import java.util.List;import cn.csdn.domain.User;public interface UserDao {boolean insert(User entity);boolean checkUser(String name);public boolean delete(User entity);public boolean update(User entity);User findById(Integer id);List findAll();
}package cn.csdn.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import cn.csdn.domain.User;
public class UserDaoImpl implements UserDao{
/*声明操作数据的对象*/
private static Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
/*静态块*/
static{
/*准备驱动程序,加载驱动*/
try {
Class.forName("com.mysql.jdbc.Driver");
/*面试必考题*/
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/job?user=root&password=admin&useUnicode=true&characterEncoding=UTF-8");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public boolean checkUser(String name) {
/*第一步：声明返回变量*/
boolean flag=false;
/*第二步：获取连接对象 conn */
/*第三步：定义sql语句 "select * from user where name='"+name+"'";*/
String sql = "select * from user where name=?";
try {
/*第四步：根据sql语句创建预处理对象*/
pstmt = conn.prepareStatement(sql);
/*第五步：为站位符 赋值*/
int index = 1;
pstmt.setString(index++, name);
/*第六步:执行查询*/
rs = pstmt.executeQuery();
/*第七步:判断*/
if(rs.next()){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
/*第八步：关闭*/
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return flag;
}
@Override
public boolean insert(User entity) {
/*第一步：声明返回变量*/
boolean flag=false;
/*第二步：获取连接对象 conn */
/*第三步：定义sql语句 ;insert  into user(name,sex,age)values('"+entity.getName()+"','"+entity.getSex()+"',"+entity.getAge()+");"*/
String sql = "insert  into user(name,sex,age)values(?,?,?);";
try {
/*第四步：根据sql语句创建预处理对象*/
pstmt = conn.prepareStatement(sql);
/*第五步：为站位符 赋值*/
int index = 1;
pstmt.setObject(index++, entity.getName());
pstmt.setObject(index++, entity.getSex());
pstmt.setObject(index++, entity.getAge());
/*第六步:执行更新*/
int i = pstmt.executeUpdate();
/*第七步:判断*/
if(i>0){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
/*第八步：关闭*/
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*记得一定要把返回的值，修改成返回的变量*/
return flag;
}
@Override
public boolean delete(User entity) {
/*第一步：声明返回变量*/
boolean flag=false;
/*第二步：获取连接对象 conn */
/*第三步：定义sql语句 ;insert  into user(name,sex,age)values('"+entity.getName()+"','"+entity.getSex()+"',"+entity.getAge()+");"*/
String sql = "delete from user where id=?";
try {
/*第四步：根据sql语句创建预处理对象*/
pstmt = conn.prepareStatement(sql);
/*第五步：为站位符 赋值*/
int index = 1;
pstmt.setInt(index++, entity.getId());
/*第六步:执行更新*/
int i = pstmt.executeUpdate();
/*第七步:判断*/
if(i>0){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
/*第八步：关闭*/
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*记得一定要把返回的值，修改成返回的变量*/
return flag;
}
public static void main(String[] args) {
/*看明白了嘛*/
UserDao userDao = new UserDaoImpl();
/*boolean flag = userDao.checkUser("redarmy");
if(flag){
System.out.println("用户名已经被占用了");
}else{
System.out.println("用户名还没有被使用,你可以使用此用户名");
}*/
/*boolean flag = userDao.insert(new User(null, "红军12", "男", 20));
if(flag){
System.out.println("注册成功");
}else{
System.out.println("注册失败");
}*/
/*List users = userDao.findAll();
for (int i = 0; i < users.size(); i++) {
User entity = (User) users.get(i);
System.out.println(entity.getId()+"  "+entity.getName());
}*/
//userDao.update(new User(7,"孙悟空","女",10000));
User entity = userDao.findById(7);
System.out.println(entity.getId()+"  "+entity.getName());
}
@Override
public List findAll() {
/*第一步:定义返回结果*/
List allentities = new ArrayList();
/*第二步:获取连接对象*/
/*第三步：定义sql语句*/
String sql ="select * from user";
try {
/*第四步：根据sql语句创建预处理对象*/
pstmt = conn.prepareStatement(sql);
/*第五步：为站位符 赋值*/
/*第六步:执行查询*/
rs = pstmt.executeQuery();
/*第七步:判断*/
while(rs.next()){
User entity = new User();
entity.setId(rs.getInt("id"));
entity.setName(rs.getString("name"));
entity.setSex(rs.getString("sex"));
entity.setAge(rs.getInt("age"));
allentities.add(entity);
}
} catch (SQLException e) {
e.printStackTrace();
}
/*第八步：关闭*/
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return allentities;
}
@Override
public boolean update(User entity) {
/*第一步：声明返回变量*/
boolean flag=false;
/*第二步：获取连接对象 conn */
/*第三步：定义sql语句 ;insert  into user(name,sex,age)values('"+entity.getName()+"','"+entity.getSex()+"',"+entity.getAge()+");"*/
String sql = "update user set name=?,sex=?,age=? where id=?";
try {
/*第四步：根据sql语句创建预处理对象*/
pstmt = conn.prepareStatement(sql);
/*第五步：为站位符 赋值*/
int index = 1;
pstmt.setObject(index++, entity.getName());
pstmt.setObject(index++, entity.getSex());
pstmt.setObject(index++, entity.getAge());
pstmt.setInt(index++, entity.getId());
/*第六步:执行更新*/
int i = pstmt.executeUpdate();
/*第七步:判断*/
if(i>0){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
/*第八步：关闭*/
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*记得一定要把返回的值，修改成返回的变量*/
return flag;
}
@Override
public User findById(Integer id) {
/*第一步:定义返回结果*/
User entity = new User();
/*第二步:获取连接对象*/
/*第三步：定义sql语句*/
String sql ="select * from user where id=?";
try {
/*第四步：根据sql语句创建预处理对象*/
pstmt = conn.prepareStatement(sql);
/*第五步：为站位符 赋值*/
int index = 1;
pstmt.setInt(index++, id);
/*第六步:执行查询*/
rs = pstmt.executeQuery();
/*第七步:判断*/
if(rs.next()){
entity.setId(rs.getInt("id"));
entity.setName(rs.getString("name"));
entity.setSex(rs.getString("sex"));
entity.setAge(rs.getInt("age"));
}
} catch (SQLException e) {
e.printStackTrace();
}
/*第八步：关闭*/
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return entity;
}
}
package cn.csdn.domain;import java.io.Serializable;/*业务Bean*/
public class User implements Serializable{/*** */private static final long serialVersionUID = 2646300928284173726L;/**递增序列*/private Integer id;/**姓名*/private String name;/*性别*/private String sex;/*年龄*/private Integer age;public User() {super();// TODO Auto-generated constructor stub}public User(Integer id, String name, String sex, Integer age) {super();this.id = id;this.name = name;this.sex = sex;this.age = age;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}}



　　----------------代码源自于redarmychen