<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<%
Statement stmt=conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from song_title"); //执行SQL语句
while(rs.next()){
out.println("<br>:"+rs.getInt(1)+"<br/>分类:"+rs.getString(2));
%>
<%
}
rs.close();
stmt.close();
%>
<body>
<%
String driverClass="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/jsp_mysql?user=root&password=root&useUnicode=true&characterEncoding=utf-8";
String username = "root";
String password = "";
Class.forName(driverClass); // 加载数据库驱动
Connection conn=DriverManager.getConnection(url, username, password); //建立连接
//静态的SQL语句实现
out.println("Statement对象执行静态的SQL语句实现");
out.println("");
静态的SQL语句实现
查询数据
ResultSet rs = stmt.executeQuery("select * from tb_user"); //执行SQL语句
while(rs.next()){
out.println("<br>用户名:"+rs.getString(2)+" 密码:"+rs.getString(3));
}
rs.close();
stmt.close();
out.println("<br><br>添加操作<br>");
添加操作
Statement addstmt=conn.createStatement();
int addrtn=addstmt.executeUpdate("insert into tb_user (username,password) values( '小明','188979230692')");
修改操作
Statement updatestmt=conn.createStatement();
int updatertn=updatestmt.executeUpdate(" update tb_user set username='Tom',password='1234admin' where username='小红'");
删除操作
Statement delstmt=conn.createStatement();
int delrtn=delstmt.executeUpdate("delete from tb_user where username='小明'");
out.println("<br><br>PreparedStatement对象执行动态的SQL语句实现<br>");
动态的SQL语句实现
动态查询数据
PreparedStatement addpStmt=conn.prepareStatement("insert into tb_user (username,password) values (?,?)");
addpStmt.setString(1, "小红");
addpStmt.setString(2, "1201245015415");
int addrtnPrep=addpStmt.executeUpdate();
动态修改数据
PreparedStatement updatepStmt=conn.prepareStatement("update tb_user set username=? , password=? where username=?");
updatepStmt.setString(1, "小妹");
updatepStmt.setString(2, "QQ298942481");
updatepStmt.setString(3, "Tom");
int updateprtn=updatepStmt.executeUpdate();
动态删除数据
PreparedStatement delpStmt=conn.prepareStatement("delete from tb_user where username=?");
delpStmt.setString(1, "user2");
int delrtn=delpStmt.executeUpdate();
conn.close();
%>
“`