login.java
package login;
import java.util.Scanner;
import cw_entity.User;
import serivice.dologin;
public class login {
public static void main(String[] args){
Scanner in=new Scanner(System.in);
System.out.println("请输入用户名:");
String name=in.nextLine();
System.out.println("请输入密码:");
String password=in.nextLine();
dologin dl=new dologin();
User user=dl.findUser(name, password);
if(user!=null){
System.out.println("欢迎用户:"+user.getName());
}else{
System.out.println("用户或密码错误!");
}
}
}
dologin.java
package login;
import java.util.Scanner;
import cw_entity.User;
import serivice.dologin;
public class login {
public static void main(String[] args){
Scanner in=new Scanner(System.in);
System.out.println("请输入用户名:");
String name=in.nextLine();
System.out.println("请输入密码:");
String password=in.nextLine();
dologin dl=new dologin();
User user=dl.findUser(name, password);
if(user!=null){
System.out.println("欢迎用户:"+user.getName());
}else{
System.out.println("用户或密码错误!");
}
}
}
//对SQL注入问题的解决
dologin1.java
package serivice;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/*
* 解决SQL注入问题
* preparedStatement
* preparedStatement是预编译对象,是Statement对象的子类
* 特点:性能要高,会把sql语句先编译,sql语句中的参数会发生变化,过滤掉用户输入的关键字。
* */
import cw_DbUtil.DBUtils;
import cw_entity.User;
public class dologin1 {
public User findUser(String name,String pwd){
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs=null;
User u=null;
try {
conn=DBUtils.getConnection(); //得到连接对象connection
String sql="select *from users where name=? and password=?";
stmt=conn.prepareStatement(sql); //得到执行SQL语句的对象PreparedStatement
//给占位符?赋值
stmt.setString(1, name);
stmt.setString(2, pwd);
rs=stmt.executeQuery();
if(rs.next()){
u=new User();
u.setId(rs.getInt(1));
u.setName(rs.getString(2));
u.setPassword(rs.getString(3));
u.setEmail(rs.getString(4));
u.setBirthday(rs.getDate(5));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, stmt, conn);
}
return u;
}
}
TestCRUD1.java
package cw_CRUD;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import cw_entity.User;
import org.junit.Test;
import cw_DbUtil.DBUtils;
public class TestCRUD1 {
@Test
public void testSelect(){
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try {
conn=DBUtils.getConnection();
stmt=conn.createStatement();
rs=stmt.executeQuery("select *from users");
List<User> list=new ArrayList<User>();
while(rs.next()){
User u=new User();
u.setId(rs.getInt(1));
u.setName(rs.getString(2));
u.setPassword(rs.getString(3));
u.setEmail(rs.getString(4));
u.setBirthday(rs.getDate(5));
list.add(u);
}
for(User user:list){
System.out.println(user);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, stmt, conn);
}
}
@Test
public void testInsert(){
Connection conn=null;
PreparedStatement stmt=null;
try {
conn=DBUtils.getConnection();
stmt=conn.prepareStatement("insert into users values(?,?,?,?)");
stmt.setInt(1, 4);
stmt.setString(2, "abc");
stmt.setString(3, "233");
//stmt.setDate(4, new java.sql.Date(System.currentTimeMillis()));
stmt.setString(4, "2015-09-01");
int i=stmt.executeUpdate();
if(i>0){
System.out.println("Success");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.closeAll(null, stmt, conn);
}
}
@Test
public void testUpdate(){
Connection conn=null;
PreparedStatement stmt=null;
try {
conn=DBUtils.getConnection();
stmt=conn.prepareStatement("update users set name=?,password=? where id=?");
stmt.setString(1, "abc");
stmt.setString(2, "123");
stmt.setInt(1, 4);
int i=stmt.executeUpdate();
if(i>0){
System.out.println("Success");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.closeAll(null, stmt, conn);
}
}
public void testDelete(){
Connection conn=null;
Statement stmt=null;
try {
conn=DBUtils.getConnection();
stmt=conn.createStatement();
int i=stmt.executeUpdate("delete from users where id=4");
if(i>0){
System.out.println("Success");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.closeAll(null, stmt, conn);
}
}
}