1、創(chuàng)建數(shù)據(jù)庫
public class JdbcUtil {
public static DataSource ds = null;
static {
try {
//1.加載配置文件
Properties p = new Properties();
//獲取字節(jié)碼目錄
String path = JdbcUtil.class.getClassLoader().getResource("db.properties").getPath();
FileInputStream in = new FileInputStream(path);
p.load(in);
//ds = BasicDataSourceFactory.createDataSource(p);
//Alibaba德魯伊連接池
ds = DruidDataSourceFactory.createDataSource(p);
}catch(Exception e) {
e.printStackTrace();
}
}
//獲取數(shù)據(jù)源
public static DataSource getDataSource() {
return ds;
}
public static Connection getConn() {
try {
// 2.連接數(shù)據(jù)
return ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 關(guān)閉資源
*/
public static void close(Connection conn,Statement st,ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、把對象寫入數(shù)據(jù)庫
public class RegistServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//設(shè)置請求編碼 與響應(yīng)的編碼
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//獲取參數(shù)
//1.接收所有參數(shù)
Map<String, String[]> parameterMap = request.getParameterMap();
User u = new User();
//2.把接收的參數(shù)封裝成User對象
try {
BeanUtils.populate(u, parameterMap);
} catch (IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
}
//3.設(shè)置uid
u.setUid(UUID.randomUUID().toString());
//4.寫入到數(shù)據(jù)庫
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
//判斷該用戶是否存在
String sql2 = "select * from user where username = ?";
User user2 = null;
try {
user2 = qr.query(sql2, new BeanHandler<User>(User.class), u.getUsername());
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
System.out.println("user2 == " + user2);
///說明用戶名已存在
if (user2 != null) {
if (u.getUsername().equals(user2.getUsername())) {
response.getWriter().write("用戶名已存在");
return;
}
}
//插入數(shù)據(jù)
String sql ="insert into user value(?,?,?,?)";
try {
qr.update(sql,u.getUid(),u.getUsername(),u.getPassword(),u.getPhone());
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3、使用sql語句操作
//學(xué)生對象
public class Student {
Integer id;
String name;
Integer 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 Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
//接口類
public interface IStudentDao {
/**
* 保存一個學(xué)生
*/
public void save(Student stu);
/**
* 刪除學(xué)生
*/
public void delete(int id);
/**
* 更新一個學(xué)生信息
*/
public void update(int id,Student stu);
/**
* 獲取指定學(xué)生
*/
public Student get(int id);
/**
* 獲取所有的學(xué)生
*/
public List<Student> getAll();
/**
* 獲取學(xué)生的總數(shù)
*/
public Integer getCount();
}
//操作類
public class StudentDaoImpl implements IStudentDao {
@Override
public void save(Student stu) {
String sql = "insert into student(name,age) values(?,?)";
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
try {
qr.update(sql, stu.getName(),stu.getAge());
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void delete(int id) {
// 3.創(chuàng)建語句
String sql = "delete from student where id = ?";
QueryRunner qr = new QueryRunner();
Connection conn = JdbcUtil.getConn();
try {
qr.update(conn, sql, id);
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void update(int id, Student stu) {
String sql = "update student set name=?, age=? where id =? ";
QueryRunner qr = new QueryRunner();
Connection conn = JdbcUtil.getConn();
try {
qr.update(conn, sql, stu.getName(), stu.getAge(), stu.getId());
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public Student get(int id) {
String sql = "select * from student where id = ?";
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
try {
return qr.query(sql, new BeanHandler<Student>(Student.class),id);
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
@Override
public List<Student> getAll() {
String sql = "select * from student ";
Connection conn = JdbcUtil.getConn();
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
//獲取全部記錄
try {
List<Student> list = qr.query(conn, sql, new BeanListHandler<Student>(Student.class));
conn.close();
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
@Override
public Integer getCount() {
String sql = "select count(*) as count from student";
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
try {
//必須long強轉(zhuǎn)
Integer num = ((Long) qr.query(sql, new ScalarHandler())).intValue();
return num;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
}
4、db.properties 的內(nèi)容，一般放在資源文件夾下
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_db?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf8
username=root
password="密碼"
maxActive=8
