##创建数据库
##连接SQL数据库
public class DBUtil {
private static String DRI="com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static Connection connectDB()throws Exception{
Class.forName(DRI);
Connection conn=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=student","sa","080105");
System.out .println("链接成功");
return conn;
}
}
链接SQL数据库时,需在创建的工程中导入SQL的jdbc
方法:Build Path->Add Jars
选择下好的jar文件。
##封装
public class UserVO {
private int id;
private String userName;
private int age;
private int sex;
private Date createDt;
public int getId() {
return id;
}
public void setId(int id) {
this.id=id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName=userName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age=age;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex=sex;
}
public Date getCreateDt() {
return createDt;
}
public void setCreateDt(Date createDt) {
this.createDt=createDt;
}
public String toString() {
return"UserVo[id="+id+"userName="+userName+",age="+age+",sex="+sex+",createDt="+createDt+"]";
}
}
##对数据库进行查增删改
public class UserDao {
public List<UserVO>queryAll()throws Exception{
Connection conn=DBUtil.connectDB();
String sql="SELECT*FROM tbl_use_info";
List<UserVO>userList=new ArrayList<UserVO>();
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
while(rs.next()) {
UserVO user =new UserVO();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setAge(rs.getInt("age"));
user.setSex(rs.getInt("sex"));
user.setCreateDt(rs.getDate("create_dt"));
userList.add(user);
}
return userList;
}
public List<UserVO>querryByParams(List<Map<String,Object>>params)throws Exception{
Connection conn=DBUtil.connectDB();
StringBuilder sql=new StringBuilder("SELECT * FROM tbl_use_info WHERE 1=1 ");
for(Map<String,Object>param:params) {
sql.append(" and ");
sql.append(" "+param.get("col")+" ");
sql.append(" "+param.get("rel")+" ");
sql.append(" "+param.get("value")+" ");
}
System.out.println(sql.toString());
List<UserVO>userList=new ArrayList<UserVO>();
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql.toString());
while(rs.next()) {
UserVO user=new UserVO();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setAge(rs.getInt("age"));
user.setSex(rs.getInt("sex"));
user.setCreateDt(rs.getDate("create_dt"));
userList.add(user);
}
return userList;
}
public void addUser(UserVO user)throws Exception{
Connection conn=DBUtil.connectDB();
String sql="INSERT INTO tbl_use_info(user_name,age,sex,create_dt)"+"VALUES(?,?,?,?)";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setString(1,user.getUserName());
pstmt.setInt(2, user.getAge());
pstmt.setInt(3, user.getSex());
pstmt.setDate(4,new Date(new java.util.Date().getTime()));
pstmt.execute();
}
public void daleteUser(int id)throws Exception{
Connection conn=DBUtil.connectDB();
String sql="DELETE FROM tbl_use_info WHERE id=?";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.execute();
}
public void updateUser(UserVO user)throws Exception{
Connection conn=DBUtil.connectDB();
String sql="UPDATE tbl_use_info SET user_name=?,age=?,sex=?"+" WHERE id=?";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setString(1,user.getUserName());
pstmt.setInt(2, user.getAge());
pstmt.setInt(3, user.getSex());
pstmt.setInt(4, user.getId());
pstmt.executeUpdate();
}
}
##测试
public class test {
public static void main(String[] args) {
UserDao dao=new UserDao();
UserVO user=new UserVO();
user.setUserName("Jack");
user.setAge(70);
user.setSex(1);
user.setId(2);
try {
dao.updateUser(user);
List<UserVO>userList=dao.queryAll();
for(UserVO users:userList)
System.out.println(users);
}catch(Exception e) {
e.printStackTrace();
}
}
public void delete() {
UserDao dao=new UserDao();
try {
dao.daleteUser(3);
List<UserVO>userList=dao.queryAll();
for(UserVO user:userList)
System.out.println(user);
}catch(Exception e) {
e.printStackTrace();
}
}
public void add() {
UserDao dao=new UserDao();
UserVO user=new UserVO();
user.setUserName("小明");
user.setAge(31);
user.setSex(1);
try {
dao.addUser(user);
List<UserVO>userList=dao.queryAll();
for(UserVO users:userList)
System.out.println(users);
}catch(Exception e) {
e.printStackTrace();
}
}
public void select1() {
UserDao dao=new UserDao();
Map<String,Object>param1=new HashMap<String,Object>();
List<Map<String,Object>>params=new ArrayList<Map<String,Object>>();
param1.put("col","user_name");
param1.put("rel", "like");
param1.put("value", "'%大白%'");
params.add(param1);
Map<String,Object>param2=new HashMap<String,Object>();
param2.put("col", "sex");
param2.put("rel", "=");
param2.put("value",0);
params.add(param2);
try {
List<UserVO>userList= dao.querryByParams(params);
for(UserVO user:userList) {
System.out.println(user);
}
}catch(Exception e) {
e.printStackTrace();
}
}
public void selectall() {
UserDao dao=new UserDao();
try {
List<UserVO>userList=dao.queryAll();
for(UserVO user:userList)
System.out.println(user);
}catch(Exception e) {
e.printStackTrace();
}
}
}