java连接mysql数据库(jsp显示和控制台显示)
很多事情,在我们没有做之前我们觉得好难,但是只要你静下心来,毕竟这些都是人搞出来的,只要你是人,那就一定可以明白。
配置:JDK1.8,MySQL5.7,eclipse:Neon Release (4.6.0),connector:mysql-connector-java-3.1.6-bin.jar
1、java连接数据库,并将结果显示在jsp页面中
这里需要用到tomcat服务器,怎么配置可以百度,下面会给出项目的工程配置和源代码以及实际运行效果。
这里,需要注意的一点是关于mysql-connector-java-3.1.6-bin.jar(版本随意,不要太旧就行)的配置,由于我新建的是“WEB->Dynamic Web project”,所以我们需要把mysql-connector-java-3.1.6-bin.jar放在WebContent->WEB-INF->lib文件夹下。不然会出现下图所示的错误。
由于是Dynamic Web project,所以不能采用Build Path->Configure Build Path->Add External JARs的形式添加mysql-connector-java-3.1.6-bin.jar,如果你执意要如此,还是会出现上图所示的找不到合适驱动的错误。
下面给出正确的工程拓扑结构及源码:
web.xml源码:
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0"> <display-name>JDBCTest1</display-name> <welcome-file-list> <welcome-file>index.html</welcome-file> <welcome-file>index.htm</welcome-file> <welcome-file>index.jsp</welcome-file> <welcome-file>default.html</welcome-file> <welcome-file>default.htm</welcome-file> <welcome-file>default.jsp</welcome-file> </welcome-file-list> </web-app>
index.jsp源码:
<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%> <% try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { System.out.println("加载数据库驱动时抛出异常,内容如下:"); e.printStackTrace(); } Connection conn = DriverManager .getConnection( "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8","root", "w513723"); Statement stmt = conn.createStatement(); ResultSet rs = stmt .executeQuery("select * from user"); while (rs.next()) { out.println("ID:"+rs.getString(1)+" "+"用户名:" + rs.getString(2)+" " + " 密码:" + rs.getString(3)+".mdb<br/>"); } rs.close(); stmt.close(); conn.close(); %> <!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> <body> </body> </html>
user表结构:
实际运行效果:
2、java连接数据库,封装创建、插入、查找接口
这个没有太多讲的,这里与上面的区别在于这是普通的java工程,不是动态web项目,所以mysql-connector-java-3.1.6-bin.jar使用Build Path->Configure Build Path->Add External JARs添加即可。
(代码源于《JAVA web程序设计 慕课版》明日科技)有时间我会增减删除和更新表的操作。
工程拓扑结构:
源代码:
JDBCUtil.java
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class JDBCUtil { /*使用静态代码块完成驱动的加载*/ static { try { String driverName = "com.mysql.jdbc.Driver"; Class.forName(driverName); } catch (Exception e) { e.printStackTrace(); } } /*提供连接的方法*/ public static Connection getConnection() { Connection con = null; try { //连接指定的MMySQL数据库,三个参数分别是:数据库地址、账号、密码 con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test?useUnicode=true&characterEncoding=utf8", "root", "w513723"); } catch (Exception e) { e.printStackTrace(); } return con; } /*关闭连接的方法*/ public static void close(ResultSet rs, Statement stmt, Connection con) { try { if (rs != null) rs.close(); } catch (Exception ex) { ex.printStackTrace(); } try { if (stmt != null) stmt.close(); } catch (Exception ex) { ex.printStackTrace(); } try { if (con != null) con.close(); } catch (Exception ex) { ex.printStackTrace(); } } }
DaoTest.java
import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DaoTest { Connection con; Statement stmt; ResultSet rs; public Connection getCon() { return con; } public Statement getStmt() { return stmt; } public ResultSet getRs() { return rs; } public DaoTest(Connection con) { this.con = con; try { stmt = con.createStatement(); } catch (SQLException e) { e.printStackTrace(); } } public void createTable() throws SQLException { stmt.executeUpdate("DROP TABLE IF EXISTS `jdbc_test` ");//删除相同名称的表 String sql = "create table jdbc_test(id int,name varchar(100)) "; stmt.executeUpdate(sql);//执行SQL System.out.println("jdbc_test表创建完毕"); } public void insert() throws SQLException { String sql1 = "insert into jdbc_test values(1,'tom') "; String sql2 = "insert into jdbc_test values(2,'张三') "; String sql3 = "insert into jdbc_test values(3,'999') "; stmt.addBatch(sql1); stmt.addBatch(sql2); stmt.addBatch(sql3); int[] results = stmt.executeBatch();//批量运行sql for (int i = 0; i < results.length; i++) { System.out.println("第" + (i + 1) + "次插入返回" + results[0] + "条结果"); } } public void select() throws SQLException { String sql = "select id,name from jdbc_test "; rs = stmt.executeQuery(sql); System.out.println("---数据库查询的结果----"); System.out.println("id\tname"); System.out.println("---------------------"); while (rs.next()) { String id = rs.getString("id"); String name = rs.getString("name"); System.out.print(id + "\t" + name+"\n"); } } public static void main(String[] args) { Connection con = JDBCUtil.getConnection(); DaoTest dao = new DaoTest(con); try { dao.createTable(); dao.insert(); dao.select(); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtil.close(dao.getRs(), dao.getStmt(), dao.getCon()); } } }
程序运行控制台输出:
数据库查询结构:
增加删除和更新操作(其实很简单的,当初为什么没有写呢?被吓到了???)
import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DaoTest { Connection con; Statement stmt; ResultSet rs; public Connection getCon() { return con; } public Statement getStmt() { return stmt; } public ResultSet getRs() { return rs; } public DaoTest(Connection con) { this.con = con; try { stmt = con.createStatement(); } catch (SQLException e) { e.printStackTrace(); } } public void createTable() throws SQLException { stmt.executeUpdate("DROP TABLE IF EXISTS `jdbc_test` ");//删除相同名称的表 String sql = "create table jdbc_test(id int,name varchar(100)) "; stmt.executeUpdate(sql);//执行SQL System.out.println("jdbc_test表创建完毕"); } public void insert() throws SQLException { String sql1 = "insert into jdbc_test values(1,'tom') "; String sql2 = "insert into jdbc_test values(2,'张三') "; String sql3 = "insert into jdbc_test values(3,'999') "; stmt.addBatch(sql1); stmt.addBatch(sql2); stmt.addBatch(sql3); int[] results = stmt.executeBatch();//批量运行sql for (int i = 0; i < results.length; i++) { System.out.println("第" + (i + 1) + "次插入返回" + results[0] + "条结果"); } } public void select() throws SQLException { String sql = "select id,name from jdbc_test "; rs = stmt.executeQuery(sql); System.out.println("---数据库查询的结果----"); System.out.println("id\tname"); System.out.println("---------------------"); while (rs.next()) { String id = rs.getString("id"); String name = rs.getString("name"); System.out.print(id + "\t" + name+"\n"); } } public void delete() throws SQLException { String sql="delete from jdbc_test where id in (1,2)"; int tmp=stmt.executeUpdate(sql); //这里函数的返回值表示成功删除了多少条数据 if (tmp<1) { System.out.println("要删除的数据不存在或删除错误!"); } else { System.out.println("成功删除"+tmp+"条数据"); } } public void update() throws SQLException { String sql="update jdbc_test set name='shuai' where id=3"; int tmp=stmt.executeUpdate(sql); //这里函数的返回值表示成功更新了多少条数据 if (tmp<1) { System.out.println("需要更新的数据不存在或更新错误!"); } else { System.out.println("成功更新"+tmp+"条数据"); } } public static void main(String[] args) { Connection con = JDBCUtil.getConnection(); DaoTest dao = new DaoTest(con); try { dao.createTable(); dao.insert(); dao.select(); dao.delete(); dao.select(); dao.update(); dao.select(); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtil.close(dao.getRs(), dao.getStmt(), dao.getCon()); } } }
运行截图: