JavaWeb学习笔记 连接mysql操作

使用MyEclipse10编写web程序，并和mysql进行连接。

使用到的jar包为 mysql-connector-java-5.1.22-bin

创建web程序，直接复制到lib文件夹中即可，如果创建java程序，则要add path

进行数据的查询，并在控制台输出

package com.duan.testsql;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class test {

	/**
	 * @param args
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		// TODO Auto-generated method stub
		/*1、注册数据库的驱动
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		上一行代码可以注册驱动，但是会使数据库驱动被注册两次
		因为Dirver类的源码中已经在静态代码块中完成了数据库驱动的注册
		避免数据库驱动重复注册使用*/
		Class.forName("com.mysql.jdbc.Driver");
		//2、通过DriverManager获取数据库连接
		String url = "jdbc:mysql://localhost:3306/ch01";
		String username = "root";
		String password = "123456";
		Connection conn = DriverManager.getConnection(url, username, password);
		//3、通过Connection获取Statement对象
		Statement stmt = conn.createStatement();
		//4、使用Statement执行sql语句
		String sql = "select * from users";
		ResultSet rs = stmt.executeQuery(sql);
		//5、操作ResultSet结果集
		System.out.println("id|name|password|email|birthday");
		while(rs.next())
		{
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String pwd = rs.getString("password");
				String email = rs.getString("email");
				Date birthday = rs.getDate("birthday");
				System.out.println(id+"|"+name+"|"+pwd+"|"+email+"|"+birthday);
			
		}
		//6、回收资源
		rs.close();
		stmt.close();
		conn.close();
	}

}

在数据库中进行添加操作

package com.duan.testsql;

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

public class test002 {

	/**
	 * @param args
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement preStmt = null;
		//注册数据库驱动
		try{
		Class.forName("com.mysql.jdbc.Driver");
		//通过DriverManager获取数据库连接
		String url = "jdbc:mysql://localhost:3306/ch01";
		String username = "root";
		String password = "123456";
		//创建引用程序与数据库拦截的Connection对象
		conn = DriverManager.getConnection(url, username, password);
		//执行sql语句
		String sql = "INSERT INTO users(name,password,email,birthday)"+"VALUES(?,?,?,?)";
		//创建执行SQL语句的PrepareStatement(sql);
		preStmt = conn.prepareStatement(sql);
		preStmt.setString(1, "王五");
		preStmt.setString(2, "123456");
		preStmt.setString(3, "ww@sina.com");
		preStmt.setString(4, "1785-3-5");
		preStmt.executeUpdate();
		}
		catch(ClassNotFoundException e)
		{
			e.printStackTrace();
		}
		//释放资源很重要，应该将最终必须执行的操作放在finally代码块中。
		finally
		{
				if(preStmt != null)
				{
					try{
							preStmt.close();
						
					}catch (SQLException e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					preStmt= null;
				}
				if(conn != null)
				{
					try
					{
						conn.close();
					}
					catch (SQLException e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					conn = null;
				}
				}
			
		}
		
	}