JavaWeb基础学习11(JDBC回顾)

JDBC

什么是JDBC:等价于中间商，将Application和数据库连接起来

1. 需要的包

<!-- 连接mysql数据库 -->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>
<!-- 以及java.sql包和jakarta.sql包 -->

2. 连接编辑MySQL

// TODO Auto-generated method stub
		 String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
		 String user = "root";
		 String password = "123456";
		//加载驱动
		 Class.forName("com.mysql.cj.jdbc.Driver");
		 //连接数据库
		 Connection connection = DriverManager.getConnection(url,user,password);
		 //向数据库发送SQL的对象statement
		 Statement statement = connection.createStatement();
		 
		 //编写SQL
		String sql = "select * from users;";
		ResultSet resultSet = statement.executeQuery(sql);
		 
		while(resultSet.next()) {
			System.out.print("id="+resultSet.getObject("id")+"\t");
			System.out.print("name="+resultSet.getObject("name")+"\t");
			System.out.print("password="+resultSet.getObject("password")+"\t");
			System.out.print("email="+resultSet.getObject("email")+"\t");
			System.out.print("birthday="+resultSet.getObject("birthday")+"\t");
			System.out.println();
		}
		 //关闭，先开后关
		resultSet.close();
		statement.close();
		connection.close();
	}

3. 用预编译编写

public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		 String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
		 String user = "root";
		 String password = "123456";
		//加载驱动
		 Class.forName("com.mysql.cj.jdbc.Driver");
		 //连接数据库
		 Connection connection = DriverManager.getConnection(url,user,password);
		 //向数据库发送SQL的对象statement
		 //编写SQL
		 String sql = "insert into users(id,name,password,email,birthday)value(?,?,?,?,?);";
		 //预编译
		 PreparedStatement statement = connection.prepareStatement(sql);
		 
		 statement.setInt(1, 4);
		 statement.setString(2, "五六七");
		 statement.setString(3, "123456");
		 statement.setString(4, "null");
		 statement.setDate(5,new Date(new java.util.Date().getDate()));
		 
		 
		 int update = statement.executeUpdate();
		 if(update>0) {
			 System.out.println("成功注册");
		 }
		 //关闭，先开后关
		
		statement.close();
		connection.close();
	}

}

用Servlet编写连接MySQL

用Servlet编写JDBC可以在maven包中实现前端与数据库的连接

1.编写一个servlet

public void init() {
		 String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
		 String user = "root";
		 String password = "123456";
		//加载驱动
		 try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 //连接数据库
		 try {
			connection = DriverManager.getConnection(url,user,password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String id = request.getParameter("id");
		String username = request.getParameter("username");
		String password = request.getParameter("password");
		String email = request.getParameter("email");
		String birthday = request.getParameter("birthday");
		 String sql = "insert into users(id,name,password,email,birthday)value(?,?,?,?,?);";
		 //预编译
		 PreparedStatement statement;
		try {
			statement = connection.prepareStatement(sql);
			 statement.setString(1, id);
			 statement.setString(2, username);
			 statement.setString(3, password);
			 statement.setString(4, email);
			 statement.setString(5,birthday);
			int update = statement.executeUpdate();
			 if(update>0) {
				 System.out.println("成功注册");
			 }
			//关闭，先开后关
				statement.close();
				connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		response.sendRedirect("/bbb/login.jsp");	
	}

2. 编写一个jsp文件

<form action="http://localhost:8080/bbb/Longin" mathod="post">
<h3>注册：</h3>
<p>
ID：<input type="number" name="id">
名字：<input type="text" name="username"><br>
密码：<input type="password" name="password">
Email:<input type="text" name="email"><br>
生日：<input type="date" name="date"><br>
<input type="submit">

3. 编辑web.xml

  <servlet>
    <description></description>
    <display-name>LonginServlet</display-name>
    <servlet-name>LonginServlet</servlet-name>
    <servlet-class>com.hai.LonginServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>LonginServlet</servlet-name>
    <url-pattern>/Longin</url-pattern>
  </servlet-mapping>

测试：