JavaWeb笔记|ODBC对接数据库

最新推荐文章于 2023-05-17 10:15:18 发布

向阳-Y.

最新推荐文章于 2023-05-17 10:15:18 发布

阅读量875

点赞数 1

分类专栏： JavaWeb 文章标签：数据库 mysql database

本文链接：https://blog.csdn.net/weixin_45951067/article/details/121356944

版权

JavaWeb 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

ODBC对接数据库

前提先链接并创建好数据库，shop
以下操作在CMD中进行

create database shop;  #创建数据库shop
use shop; #调用数据库shop

#创建数据表，并添加字段
create table user(id int auto_increment primary key,
username varchar(20)
password varchar(20)
age int(3));

#插入数据
insert into user(username,password,age)values('user1','123',20);
show tables; #查看表
select * from user;从表中查看字段

index.jsp

以下代码实现每当运行一次就往数据中插入一条数据
请添加图片描述

1.先把mysql架构包导入到WEB-INF/lib目录下
2.导入完成后创建index.jsp文件并输入以下代码

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <body>
    <%
    Class.forName("com.mysql.jdbc.Driver");
    String url="jdbc:mysql://localhost:3306/shop";
    String user="root";
    String password="root";
    Connection connection = DriverManager.getConnection(url,user,password);
    Statement st = connection.createStatement();
    //Statement有如下三种方法
    //execute(sql) 从来不用
    //executeQuery(sql) 只用于select语句，返回ResultSet对象
    //executeUpdate(sql) 用于除了select之外的语句(insert,update,delete,create table)
    //                   返回一个整数int，表示sql语句影响的记录条数
    String sql="insert into user(username,password,age)values('user2','111',30)";
    int i = st.executeUpdate(sql);
    if(i==1)
    	out.println("插入成功！");
    else
    	out.println("插入失败！");
    %>
  </body>
</html>

list.jsp

以下代码实现显示出插入的结果：
请添加图片描述

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <body>
    <%
    Class.forName("com.mysql.jdbc.Driver");
    String url="jdbc:mysql://localhost:3306/shop";
    String user="root";
    String password="root";
    Connection connection = DriverManager.getConnection(url,user,password);
    Statement st = connection.createStatement();
    //Statement有如下三种方法
    //execute(sql) 从来不用
    //executeQuery(sql) 只用于select语句，返回ResultSet对象
    //executeUpdate(sql) 用于除select之外的语句(insert,update,delete,create table)
    //                   返回一个整数int，表示sql语句影响的记录条数
    String sql="select * from user";
    ResultSet rs=st.executeQuery(sql);
    while(rs.next()){
    	int id = rs.getInt("id");
    	String username=rs.getString("username");
    	String pwd=rs.getString("password");
    	int age =rs.getInt("age");
    	out.println(id+" - " + username + " - " + pwd + " - " + age + "<br/>");
    	
    }
    %>
  </body>
</html>