USE u ;
CREATE TABLE us(
id INT(20) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex VARCHAR(20) NOT NULL,
age INT(20) NOT NULL
);
SELECT * FROM us;
INSERT INTO us(id,NAME,sex,age ) VALUES (1,'zs','n',13);
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBConn {
private static final String URL = "jdbc:mysql://localhost:3306/u";
private static final String USERNAME = "root";
private static final String PWD = "root";
// 获取数据库的连接
public static Connection getCon() throws Exception {
// a.导入驱动，加载具体的驱动类
Class.forName("com.mysql.jdbc.Driver");// 加载具体的驱动类
// b.与数据库建立连接
Connection con = DriverManager.getConnection(URL, USERNAME, PWD);
return con;
}
public static void realse(ResultSet rs,Statement stmt,Connection con) {
if(rs!=null) {
try {
rs.close();
}catch(Exception e) {
e.printStackTrace();
}
rs=null;
}
if(stmt!=null) {
try {
stmt.close();
}catch(Exception e) {
e.printStackTrace();
}
stmt=null;
}
if(con!=null) {
try {
con.close();
}catch(Exception e) {
e.printStackTrace();
}
con=null;
}
}
}
package dao;
public class DeleteUserTest {
public static void main(String[] args) {
UserD dao = new UserD();
boolean flag =dao.delete(1);
System.out.println(flag);
}
}
package dao;
import java.util.List;
public class FindAllUserTest {
public static void main(String[] args){
UserD dao = new UserD();
List<UserDao> list = dao.findAllUser();
System.out.println(list.size());
}
}
package dao;
public class FindUserByIdTest {
public static void main(String[] args) {
UserD dao = new UserD();
UserDao u = dao.findUserById(1);
System.out.println(u.getName());
}
}
package dao;
import dao.UserDao;
public class JdbcInsertTest {
public static void main(String[] args){
UserD dao = new UserD();
UserDao us = new UserDao();
us.setId(2);
us.setName("zs");
us.setSex("n");
us.setAge(20);
dao.insert(us);
}
}
package dao;
public class UpdateUserTest {
public static void main(String[] args) {
UserD dao = new UserD();
UserDao u = new UserDao();
u.setId(1);
u.setName("ls");
u.setAge(30);
boolean flag = dao.update(u);
System.out.println(flag);
}
}
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class UserD {
//添加
public boolean insert(UserDao us) {
Connection con = null;
Statement stmt = null;
try {
//获取连接对象
con=DBConn.getCon();
//获取执行sql语句对象
stmt = con.createStatement();
//执行sql
String sql ="insert into us(id,name,sex,age)"+"values('"
+us.getId()+"','"
+us.getName()+"','"
+us.getSex()+"','"
+us.getAge()+"'"
+")";
int row =stmt.executeUpdate(sql);
if(row>0) {
return true;
}
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
DBConn.realse(null, stmt, con);
}
return false;
}
//查询全部
public List<UserDao> findAllUser(){
Connection con = null;
Statement stmt = null;
ResultSet rs =null;
try {
//获取连接对象
con=DBConn.getCon();
//获取执行sql语句对象
stmt = con.createStatement();
//执行sql
String sql="select * from us";
rs = stmt.executeQuery(sql);
List<UserDao> list = new ArrayList<UserDao>();
while(rs.next()) {
UserDao us = new UserDao();
us.setId(rs.getInt("id"));
us.setName(rs.getString("name"));
us.setSex(rs.getString("sex"));
us.setAge(rs.getInt("age"));
list.add(us);
}
return list;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
DBConn.realse(rs, stmt, con);
}
}
//根据id查询
public UserDao findUserById(int id){
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs =null;
try {
//获取连接对象
con=DBConn.getCon();
//获取执行sql语句对象
String sql="select * from us where id=?";
stmt = con.prepareStatement(sql);
//执行sql
stmt.setInt(1, id);
rs = stmt.executeQuery();
if(rs.next()) {
UserDao us = new UserDao();
us.setId(rs.getInt("id"));
us.setName(rs.getString("name"));
us.setSex(rs.getString("sex"));
us.setAge(rs.getInt("age"));
return us;
}
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
DBConn.realse(rs, stmt, con);
}
return null;
}
//修改
public boolean update(UserDao us) {
Connection con = null;
PreparedStatement stmt = null;
try {
//获取连接对象
con=DBConn.getCon();
//获取执行sql语句对象
String sql ="update us set name = ?,age = ? where id = ?";
stmt = con.prepareStatement(sql);
//执行sql
stmt.setString(1, us.getName());
stmt.setInt(2, us.getAge());
stmt.setInt(3, us.getId());
int row =stmt.executeUpdate();
if(row>0) {
return true;
}
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
DBConn.realse(null, stmt, con);
}
return false;
}
//删除
public boolean delete(int id) {
Connection con = null;
PreparedStatement stmt = null;
try {
//获取连接对象
con=DBConn.getCon();
//获取执行sql语句对象
String sql ="delete from us where id = ?";
stmt = con.prepareStatement(sql);
//执行sql
stmt.setInt(1, id);
int row =stmt.executeUpdate();
if(row>0) {
return true;
}
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
DBConn.realse(null, stmt, con);
}
return false;
}
}
package dao;
public class UserDao {
private int id;
private String name;
private String sex;
private int age;
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
