一、JDBC
1.创建数据库连接
java语言发送sql语言控制数据库
1.导入jar包 2.加载驱动类:com.mysql.cj.jdbc.Driver( mysql8.0版本) 3.给出url: 4.给出username: 5.给出password 6.得到Connection对象:
连接数据库的时候要注意时区问题serverTimezone=UTC
Class.forName("com.mysql.cj.jdbc.Driver");//创建对象(加载驱动) String url = "jdbc:mysql://localhost:3306/mytest?serverTimezone=UTC"; String username = "root"; String password = "2157"; Connection con = DriverManager.getConnection(url, username, password);//连接对象 System.out.println(con);//判断是否连接成功
2.使用statement对象
使用statement对象,发送sql语句操作
Statement stm = con.createStatement(); String sql1 = "insert into student(name,age) values('aaa',2)";//增加 String sql2 = "delete from student where id = 2";//删除 String sql3 = "update student set name='李四' where id=3";//修改更新 int i = stm.executeUpdate(sql3);//执行sql语句并返回值 System.out.println(i);//打印执行sql语句的结果
3.ResultSet结果集
查询结果
String sql4 = "select * from student";//查询表中所有数据 ResultSet rs = stm.executeQuery(sql4);//执行sql语句并且返回ResultSet结果集 while(rs.next()) {//遍历结果集中的所有数据 int id = rs.getInt(1);//得到第一列的数据 String name = rs.getString(2);//第二列 int age = rs.getInt(3);//第三列 System.out.println("id:"+id+",name:"+name+",age:"+age); }
4.关闭连接
(注意关闭顺序) rs.close();//顺序关闭 stm.close(); con.close();
5.PreparedStatement接口
是Statement接口的子接口
有效解决了sql注入的问题
pstm.executeQuery()中没有参数
String sql2 = "select * from student where name like ?"; PreparedStatement pstm = con.prepareStatement(sql2);//能够解决sql注入问题 pstm.setString(1, "%a%"); // pstm.setInt(2, 4); // int i = pstm.executeUpdate(); ResultSet rs = pstm.executeQuery();//注意不能带参数 while(rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); int age = rs.getInt(1); System.out.println("id:"+id+",name:"+name+",age:"+age); } rs.close(); pstm.close(); con.close();
二、JDBC实例
1.建立连接
1.1 建立数据库连接(把连接数据库的代码封装到getCon()方法里面)
// 1.获得数据库连接 public Connection getCon() { try { Class.forName("com.mysql.cj.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/aaa?serverTimezone=UTC"; String username = "root"; String password = "2157"; con = DriverManager.getConnection(url, username, password);//连接对象 } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }//创建对象(加载驱动) return con; }
1.2 把关闭数据库连接,会话开启的代码封装到colseAll()方法里面
// 2.关闭资源 public void closeAll() { try { if(rs!=null) { rs.close(); } if(pstm!=null) { pstm.close(); } if(con!=null) { con.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
2.创建实体类
2.1创建Teacher的实体类,包含以下属性
private int t_id; private String t_name; private String address; private String phone;
2.2提供get set toString 方法,有参构造和无参构造
3.公共类BaseDao
3.1公共的更新方法 Object...o是可变长数组
//公共的更新方法Object...是可变长数组 protected int update(String sql,Object...o) throws SQLException { DBUtil dbUtil = new DBUtil(); Connection con = dbUtil.getCon(); PreparedStatement pstm = con.prepareStatement(sql);//能够解决sql注入问题 if(o!=null) { for (int i = 0; i < o.length; i++) { pstm.setObject(i+1, o[i]);//第i+1列 数组中的第一个数 } } int i = pstm.executeUpdate(); dbUtil.closeAll(); return i; }
3.2 公共的查询方法
//公共的查询方法 protected ResultSet query(String sql, Object...o) throws SQLException { DBUtil dbUtil = new DBUtil(); Connection con = dbUtil.getCon(); PreparedStatement pstm = con.prepareStatement(sql);//能够解决sql注入问题 if(o!=null) { for (int i = 0; i < o.length; i++) { pstm.setObject(i+1, o[i]); } } ResultSet rs = pstm.executeQuery(); return rs; }
4.提供接口
给接下来些的dao层方法提供接口
public interface ITeacher { //新增 public int add(Teacher t) throws Exception; //修改 public int update(Teacher t) throws Exception; //删除 public int del(int t_id) throws Exception; //查询所有(列表) public List<Teacher> getAll() throws Exception; //模糊查询 public List<Teacher> getByName(String input_name) throws Exception; }
5.TeacherDao
继承公共类BaseDao 接口ITeacher,实现接口的方法 首先写增删改
@Override public int add(Teacher t) throws Exception { return super.update("insert into teacher(t_name,address,phone) values(?,?,?)", t.getT_name(),t.getAddress(),t.getPhone()); } @Override public int update(Teacher t) throws Exception { return super.update("update teacher set t_name = ?,address = ?,phone = ? where t_id = ?", t.getT_name(),t.getAddress(),t.getPhone(),t.getT_id()); } @Override public int del(int t_id) throws Exception { return super.update("delete from teacher where t_id = ?", t_id); }
查询全部的方法
@Override public List<Teacher> getAll() throws Exception { List<Teacher> list = new ArrayList<>(); ResultSet rs = super.query("select * from teacher"); while (rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); String address = rs.getString(3); String phone = rs.getString(4); Teacher student = new Teacher(id, name, address,phone); list.add(student); } return list; }
模糊查询
@Override public List<Teacher> getByName(String input_name) throws Exception { List<Teacher> list = new ArrayList<>(); ResultSet rs = super.query("select * from teacher where t_name like ?","%"+input_name+"%"); while (rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); String address = rs.getString(3); String phone = rs.getString(4); Teacher teacher = new Teacher(id, name, address,phone); list.add(teacher); } return list; }
6.测试
public static void main(String[] args) throws Exception { Teacher teacher = new Teacher(); TeacherDao tdao = new TeacherDao(); teacher.setT_name("lisi"); teacher.setAddress("beijing"); teacher.setPhone("18309564346"); // int add = tdao.add(teacher); // System.out.println(add); // tdao.del(1); // teacher.setT_id(2); // tdao.update(teacher); // 查询所有 // List<Teacher> allTeacher = tdao.getAll(); // System.out.println(allTeacher); // 模糊查询 List<Teacher> byName = tdao.getByName("l"); System.out.println(byName);
三、JavaWeb
<body> <%! //jsp声明可以定义java的方法 public int add(int a,int b){ return a+b; } %> <h1>5+56= <%= add(5,56) %></h1> </body>
1.jsp内置对象
out对象、向向客户端输出
get请求post
request请求对象
session对象
application对象
pageContext域对象
2.实现servlet的方式
手动实现/自动实现(注意配置文件)
3.JavaWeb监听器
Servlet Listener Filter
4.JavaWeb经典三层架构
web层:jsp和servlet//Spring mvc
业务层:
数据层:
四、JavaWeb实例
1、用户注册、验证、登录