jdbc

最新推荐文章于 2021-12-30 16:36:05 发布

王乾坤

最新推荐文章于 2021-12-30 16:36:05 发布

阅读量597

点赞数

分类专栏： java-wab

java-wab 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

jdbcUtil.java

package cn.itcast.jdbc.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class JdbcUtils {

	private static final String username;
	private static final String password;;
	private static final String url;

	static {

		username = ResourceBundle.getBundle("mysql").getString("username");
		password = ResourceBundle.getBundle("mysql").getString("password");
		url = ResourceBundle.getBundle("mysql").getString("url");

		// 1.加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

	}

	// 得到一个可以获取Connection对象的方法.

	public static Connection getConnection() throws ClassNotFoundException,
			SQLException {

		// 2.获取连接对象.
		Connection con = DriverManager.getConnection(url, username, password);

		return con;

	}

	// 关闭
	public static void closeConnection(Connection con) throws SQLException {
		if (con != null) {
			con.close();
		}
	}

	public static void closeStatement(Statement st) throws SQLException {
		if (st != null) {
			st.close();
		}
	}

	public static void closeResultSet(ResultSet rs) throws SQLException {
		if (rs != null) {
			rs.close();
		}
	}
}

mysql.properties

username=root
password=abc
url=jdbc\:mysql\:///day18

JdbcDemo1.java

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//快速入门
public class JdbcDemo1 {

	public static void main(String[] args) throws SQLException,
			ClassNotFoundException {

		// DriverManager.registerDriver(new Driver()); // 加载驱动

		Class.forName("com.jdbc.mysql.Driver"); // 通过反射让Driver类加载，底层静态代码块执行，就注册了驱动

		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/day18", "root", "abc"); // 获取连接

		Statement st = con.createStatement(); // 获取操作sql语句的对象

		ResultSet rs = st.executeQuery("select * from user"); // 操作sql语句,并得到结果

		// rs.next();
		//
		// System.out.println(rs.getObject("username"));

		// 遍历结果集
		while (rs.next()) {

			System.out.print("id:" + rs.getInt("id") + "\t");
			System.out.print("username:" + rs.getString("username") + "\t");
			System.out.print("password:" + rs.getString("password") + "\t");
			System.out.print("email:" + rs.getString("email") + "\t");

			System.out.println();
		}
	}
}

JdbcDemo2.java

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//快速入门
public class JdbcDemo2 {

	public static void main(String[] args) throws SQLException,
			ClassNotFoundException {
		
		
		Class.forName("com.mysql.jdbc.Driver"); // 通过反射让Driver类加载，底层静态代码块执行，就注册了驱动

		Class.forName("oracle.jdbc.driver.OracleDriver"); // 加载了oracle驱动

		String mysqlurl = "jdbc:mysql:///day18";
		
		String oracleurl="jdbc:oracle:thin:@localhost:1521:sid"; //oracle 的url

		Connection con = DriverManager.getConnection(mysqlurl, "root", "abc"); // 获取连接
		
		System.out.println(con);

	}
}

JdbcDemo3.java

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//异常处理与资源释放
public class JdbcDemo3 {

	public static void main(String[] args) {
		String username = "root";
		String password = "abc";
		String url = "jdbc:mysql:///day18";
		String sql = "select * from user";

		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");

			// 2.获取连接对象.
			con = DriverManager.getConnection(url, username, password);

			// 3.获取操作sql语句的对象
			st = con.createStatement();

			// 4.执行sql，得到ResultSet
			rs = st.executeQuery(sql);

			// 5.遍历rs
			while (rs.next()) {

				System.out.print("id:" + rs.getInt("id") + "\t");
				System.out.print("username:" + rs.getString("username") + "\t");
				System.out.print("password:" + rs.getString("password") + "\t");
				System.out.print("email:" + rs.getString("email") + "\t");

				System.out.println();
			}

		} catch (ClassNotFoundException e) {
			System.out.println("驱动类不存在");
		} catch (SQLException e) {
			System.out.println("连接mysql失败");
		} finally {
			// 6.关闭资源
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (st != null)
					st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

JdbcDemo4.java

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

//异常处理与资源释放---执行 update操作
public class JdbcDemo4 {

	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入要修改的用户的id");
		int id = sc.nextInt();
		System.out.println("请输入修改后的密码:");
		String pwd = sc.next();

		String username = "root";
		String password = "abc";
		String url = "jdbc:mysql:///day18";
		String sql = "update user set password='"+pwd+"' where id='"+id+"'";
		
		//update user set password='123' where id='1';

		Connection con = null;
		Statement st = null;

		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");

			// 2.获取连接对象.
			con = DriverManager.getConnection(url, username, password);

			// 3.获取操作sql语句的对象
			st = con.createStatement();

			// 4.执行update语句
			int row = st.executeUpdate(sql);

			if (row != 0) {
				System.out.println("修改成功");
			}

		} catch (ClassNotFoundException e) {
			System.out.println("驱动类不存在");
		} catch (SQLException e) {
			System.out.println("连接mysql失败");
		} finally {
			// 6.关闭资源

			try {
				if (st != null)
					st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

JdbcDemo5.java

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import cn.itcast.jdbc.utils.JdbcUtils;

//sql  crud
public class JdbcDemo5 {
	
	@Test
	public void delete(){
		String sql = "delete from user where id=5";
		// 1.得到Connection对象
		Connection con = null;
		Statement st = null;

		try {
			con = JdbcUtils.getConnection();

			// 2.得到执行sql语句的Statement对象
			st = con.createStatement();

			// 3.执行sql语句 executeUpdate
			int row = st.executeUpdate(sql);
			if (row != 0) {
				System.out.println("删除成功");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 5.关闭
			try {

				JdbcUtils.closeStatement(st);
				JdbcUtils.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	@Test
	public void insert(){
		String sql = "insert into user values(null,'李四','kkk','ls@163.com')";
		// 1.得到Connection对象
		Connection con = null;
		Statement st = null;

		try {
			con = JdbcUtils.getConnection();

			// 2.得到执行sql语句的Statement对象
			st = con.createStatement();

			// 3.执行sql语句 executeUpdate
			int row = st.executeUpdate(sql);
			if (row != 0) {
				System.out.println("插入成功");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 5.关闭
			try {

				JdbcUtils.closeStatement(st);
				JdbcUtils.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	@Test
	public void update() {
		String sql = "update user set username='张三' where id=1";
		// 1.得到Connection对象
		Connection con = null;
		Statement st = null;

		try {
			con = JdbcUtils.getConnection();

			// 2.得到执行sql语句的Statement对象
			st = con.createStatement();

			// 3.执行sql语句 executeUpdate
			int row = st.executeUpdate(sql);
			if (row != 0) {
				System.out.println("修改成功");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 5.关闭
			try {

				JdbcUtils.closeStatement(st);
				JdbcUtils.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	@Test
	public void select() {

		String sql = "select * from user";

		// 1.得到Connection对象
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			con = JdbcUtils.getConnection();

			// 2.得到执行sql语句的Statement对象
			st = con.createStatement();

			// 3.执行sql语句
			rs = st.executeQuery(sql);

			// 4.遍历rs
			while (rs.next()) {

				String id = rs.getString("id");
				String username = rs.getString("username");
				int password = rs.getInt("password");
				String email = rs.getString("email");

				System.out.println("id:" + id + "  username:" + username
						+ "  password:" + password + "  email:" + email);

			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 5.关闭
			try {
				JdbcUtils.closeResultSet(rs);
				JdbcUtils.closeStatement(st);
				JdbcUtils.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

JdbcDemo6 .java

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import cn.itcast.jdbc.utils.JdbcUtils;

//滚动结果集
public class JdbcDemo6 {
	@Test
	public void select() {

		String sql = "select * from user";

		// 1.得到Connection对象
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			//得到一个可以获取Connection对象的方法.
			con = JdbcUtils.getConnection();

			// 2.得到执行sql语句的Statement对象
			st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

			// 3.执行sql语句
			rs = st.executeQuery(sql);

			// 4.使用滚动结果集

			rs.absolute(2);
			
			rs.updateString("username", "刘六");
			rs.updateString("password", "kkk");
			rs.updateRow();

			// String id = rs.getString("id");
			// String username = rs.getString("username");
			// int password = rs.getInt("password");
			// String email = rs.getString("email");
			//
			// System.out.println("id:" + id + "  username:" + username
			// + "  password:" + password + "  email:" + email);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 5.关闭
			try {
				JdbcUtils.closeResultSet(rs);
				JdbcUtils.closeStatement(st);
				JdbcUtils.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

JdbcDemo7 .java

package cn.itcast.jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import cn.itcast.jdbc.utils.JdbcUtils;

//大二进制处理
public class JdbcDemo7 {

	// 存入大数据
	@Test
	public void insert() throws ClassNotFoundException, SQLException,
			FileNotFoundException {

		String sql = "insert into myblob values(null,?)";

		// 1.得到Connection
		Connection con = JdbcUtils.getConnection();
		// 2.得到PreparedStatement
		PreparedStatement pst = con.prepareStatement(sql);

		// 3.插入数据
		File file = new File("D:\\java0420\\workspace\\day18_1\\Beyond.mp3");
		FileInputStream inputStream = new FileInputStream(file);
		// pst.setBlob(1, inputStream); //oracle中好使
		// java.lang.AbstractMethodError:
		// com.mysql.jdbc.PreparedStatement.setBlob(ILjava/io/InputStream;)V
		// 原因:mysql驱动不支持setBlob方法。

		// pst.setBinaryStream(1, inputStream); //也不支持
		// pst.setBinaryStream(1,intputStream,long length);

		pst.setBinaryStream(1, inputStream, (int) file.length());

		// 4.执行
		pst.executeUpdate();

		// 5.关闭
		pst.close();
		con.close();
	}

	// 取出
	@Test
	public void getblob() throws ClassNotFoundException, SQLException,
			IOException {
		String sql = "select * from myblob where id=?";

		// 1.得到Connection
		Connection con = JdbcUtils.getConnection();
		// 2.得到PreparedStatement
		PreparedStatement pst = con.prepareStatement(sql);

		// 3.执行sql语句
		pst.setInt(1, 1);
		ResultSet rs = pst.executeQuery();

		// 4.遍历结果集

		if (rs.next()) {

			// rs.getBlob("msg");
			InputStream is = rs.getBinaryStream("msg");

			// 就完成一个文件复制操作
			FileOutputStream fos = new FileOutputStream("d:/海阔天空.mp3");

			int len = -1;
			byte[] b = new byte[1024 * 100];

			while ((len = is.read(b)) != -1) {
				fos.write(b, 0, len);
				fos.flush();
			}
			fos.close();
			is.close();

		}
		rs.close();
		pst.close();
		con.close();

	}
}

JdbcDemo8.java

package cn.itcast.jdbc;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import cn.itcast.jdbc.utils.JdbcUtils;

//大文本存取
public class JdbcDemo8 {

	@Test
	public void add() throws ClassNotFoundException, SQLException,
			FileNotFoundException {
		String sql = "insert into mytext values(null,?)";
		// 1.得到Connection
		Connection con = JdbcUtils.getConnection();
		// 2.得到PreparedStatement
		PreparedStatement pst = con.prepareStatement(sql);

		// 3.操作
		// pst.setClob(parameterIndex, x) //在oracle中它的大文本叫clob类型.

		File file = new File("a.txt");
		FileReader fr = new FileReader(file);

		pst.setCharacterStream(1, fr, (int) file.length());

		pst.executeUpdate();

		pst.close();
		con.close();

	}

	@Test
	public void get() throws ClassNotFoundException, SQLException, IOException {
		String sql = "select * from mytext where id=?";
		// 1.得到Connection
		Connection con = JdbcUtils.getConnection();
		// 2.得到PreparedStatement
		PreparedStatement pst = con.prepareStatement(sql);

		// 3.操作
		pst.setInt(1, 1);
		ResultSet rs = pst.executeQuery();

		if (rs.next()) {
			Reader r = rs.getCharacterStream("msg");

			BufferedReader br = new BufferedReader(r);

			BufferedWriter bw = new BufferedWriter(new FileWriter("d:/a.txt"));

			String line = null;

			while ((line = br.readLine()) != null) {
				bw.write(line);
				bw.newLine();
				bw.flush();
			}
			bw.close();
			br.close();

		}

		rs.close();
		pst.close();
		con.close();

	}
}

JdbcDemo9.java

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import cn.itcast.jdbc.utils.JdbcUtils;

//Statement执行批处理
public class JdbcDemo9 {

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
		String sql0="drop table if exists student";
		String sql1 = "create table student(id int,name varchar(20))";
		String sql2 = "insert into student values(1,'tom1')";
		String sql3 = "insert into student values(2,'tom2')";
		String sql4 = "insert into student values(3,'tom3')";
		String sql5 = "insert into student values(4,'tom4')";
		String sql6 = "update student set name='fox' where id=3";
		// 1.得到Connection对象
		Connection con = JdbcUtils.getConnection();

		// 2.得到Statement对象
		Statement st = con.createStatement();

		// 3.批处理

		// 3.1 将要执行的sql语句通过addBatch加入到批处理中.
		st.addBatch(sql0);
		st.addBatch(sql1);
		st.addBatch(sql2);
		st.addBatch(sql3);
		
		st.executeBatch();
		st.clearBatch();
		
		st.addBatch(sql4);
		st.addBatch(sql5);
		st.addBatch(sql6);
		// 3.2执行批处理
		st.executeBatch();

		// 4.关闭
		st.close();
		con.close();
	}
}

JdbcDemo10.java

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import cn.itcast.jdbc.utils.JdbcUtils;

//PreparedStatement执行批处理
public class JdbcDemo10 {

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {

		String sql = "insert into student values(?,?)";
		// 1.得到Connection
		Connection con = JdbcUtils.getConnection();

		// 2.得到PreparedStatement
		PreparedStatement pst = con.prepareStatement(sql);

		for (int i = 0; i < 1000; i++) {
			pst.setInt(1, i);
			pst.setString(2, "tom" + i);
			pst.addBatch();  //添加sql语句到批处理

			if (i % 100 == 0 && i != 0) {
				pst.executeBatch();  //执行批处理
				pst.clearBatch(); //清空
			}
		}

		pst.executeBatch();
		pst.close();
		con.close();
	}
}

_LoginServlet.java

package cn.itcast.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import cn.itcast.jdbc.utils.JdbcUtils;
//存在问题的操作   sql注入
public class _LoginServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// 1.设置编码
		request.setCharacterEncoding("utf-8");
		// 2.得到请求信息
		String username = request.getParameter("username");
		String password = request.getParameter("password");

		// 3.在数据库中校验usename,password是否正确

		String sql = "select * from user where username='" + username
				+ "' and password='" + password + "'";
		
		System.out.println(sql);
		// 3.1 获取Connection
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			con = JdbcUtils.getConnection();
			// 3.2得到执行sql语句的Statement
			st = con.createStatement();

			// 3.3执行sql语句
			rs = st.executeQuery(sql);

			if (rs.next()) {
				// 登录成功
				response.sendRedirect(request.getContextPath() + "/success.jsp");
				return;
			} else {
				// 登录失败
				request.setAttribute("login.message", "用户名或密码错误");
				request.getRequestDispatcher("/login.jsp").forward(request,
						response);
				return;
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtils.closeStatement(st);
				JdbcUtils.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

}

LoginServlet .java

package cn.itcast.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import cn.itcast.jdbc.utils.JdbcUtils;

//使用PreparedStatement解决sql注入
public class LoginServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// 1.设置编码
		request.setCharacterEncoding("utf-8");
		// 2.得到请求信息
		String username = request.getParameter("username");
		String password = request.getParameter("password");

		// 3.在数据库中校验usename,password是否正确

		String sql = "select * from user where username=? and password=?";

		// 3.1 获取Connection
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			con = JdbcUtils.getConnection();
			// 3.2得到执行sql语句的PreparedStatement
			st = con.prepareStatement(sql);
			st.setString(1, username);
			st.setString(2, password);

			// 3.3执行sql语句
			rs = st.executeQuery();

			if (rs.next()) {
				// 登录成功
				response.sendRedirect(request.getContextPath() + "/success.jsp");
				return;
			} else {
				// 登录失败
				request.setAttribute("login.message", "用户名或密码错误");
				request.getRequestDispatcher("/login.jsp").forward(request,
						response);
				return;
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtils.closeStatement(st);
				JdbcUtils.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

}

login.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    
    <title>My JSP 'index.jsp' starting page</title>
	
  </head>
  
  <body>
  	${requestScope["login.message"] }<br>
   <form action="${pageContext.request.contextPath}/login" method="post">
   		username:<input type="text" name="username"><br>
  	 	password:<input type="password" name="password"><br>
   		<input type="submit" value="login">
   </form>
  </body>
</html>

success.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>success</title>
</head>

<body>登录成功
</body>
</html>

王乾坤

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
jdbc

jdbcUtil.javapackage cn.itcast.jdbc.utils;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java
复制链接

扫一扫