常用的sql语句

查询
select 列名1,列名2,列名3 from 表名 where n账号 = ? and 密码= ? and 状态= ?

@Override
public boolean login(Person person) throws Exception {

	Boolean flag = false;

	String sql = "select active,image,id from person where name =? and password=? and flag=?";
	DataBaseConnection dbc = null;
	PreparedStatement psmt = null;
	dbc = new DataBaseConnection();
	try {
		psmt = dbc.getConnection().prepareStatement(sql);
		psmt.setString(1, person.getName());
		psmt.setString(2, person.getPassword());
		psmt.setString(3, person.getFlag());
		ResultSet rs = psmt.executeQuery();
		System.out.println(2);

		if (rs.next()) {
			flag = true;
			person.setActive(rs.getString(1));
			person.setImage(rs.getString(2));
			person.setId(rs.getInt(3));

		}

	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		dbc.close();
	}

	return flag;
}

插入，注册

@Override
public void register(Person person) throws Exception {
// TODO Auto-generated method stub
// 完成注册
String sql = “insert into person(id,name,password,email,flag,image)”;
DataBaseConnection dbc = null;
PreparedStatement psmt = null;
dbc = new DataBaseConnection();
try {
psmt = dbc.getConnection().prepareStatement(sql);
// psmt.setInt(1, person.getId()); id自增
psmt.setString(1, person.getName());
psmt.setString(2, person.getPassword());
psmt.setString(3, person.getEmail());
psmt.setString(4, person.getFlag());
psmt.setString(5, person.getImage());
psmt.executeQuery();
} catch (Exception e) {
e.printStackTrace();
} finally {
dbc.close();
}

}

检查

//检查用户是否存在
@Override
public boolean checkUser(Person person) throws Exception {
	// TODO Auto-generated method stub
	boolean b=false;
	String sql = "select name from person where name=?";
	DataBaseConnection dbc = null;
	PreparedStatement psmt = null;
	dbc = new DataBaseConnection();
	try {
		psmt=dbc.getConnection().prepareStatement(sql);
		psmt.setString(1, person.getName());
		ResultSet rs=psmt.executeQuery();
		if(rs.next()) {
			return b=true;
		}
	}catch(Exception e) {
		e.printStackTrace();
	}finally {
		dbc.close();
	}
	return b;
}

// 根据id查询用户
@Override
public boolean checkUserById(String id) throws Exception {
// TODO Auto-generated method stub
boolean b = false;
String sql = “select id from person where id=?”;
PreparedStatement pstmt = null;
DataBaseConnection dbc = null;
dbc = new DataBaseConnection();
try {
pstmt.getConnection().prepareStatement(sql);
pstmt.setString(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
b = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
dbc.close();
}
return b;
}

// 根据id查询用户
@Override
public Person checkPersonById(String id) throws Exception {
	// TODO Auto-generated method stub
	Person p=null;
	String sql = "select * from person where id=?";
	PreparedStatement pstmt = null;
	DataBaseConnection dbc = null;
	dbc = new DataBaseConnection();
	try {
		pstmt.getConnection().prepareStatement(sql);
		pstmt.setString(1,id);
		pstmt.setString(2,"name");
		pstmt.setString(3,"password");
		pstmt.setString(4,"email");
		pstmt.setString(5,"flag");
		pstmt.setString(6,"image");
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()) {
		    p=new Person();
		    p.setId(rs.getInt(1));
		    p.setName(rs.getString(2));
		    p.setPassword(rs.getString(3));
		    p.setEmail(rs.getString(4));
		    p.setFlag(rs.getString(5));
		    p.setImage(rs.getString(6));
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		dbc.close();
	}
	return p;
}

}