jdbc连接数据库并进行操作

JDBC：Java Database Connection，表示数据库连接，是java中专门提供的一组用于操作数据库的标准，所有的数据库生产商如果要是想为java提供支持，则必须支持此标准，既然是标准的话，所以说JDBC实际上是一套类库的接口。主要的操作类和接口：Connection接口、Statement接口、PreparedStatement接口、ResultSet接口、DriverManager类

/*JDBC  程序访问数据库的步骤（用第一种Statement举例，详见下面代码）

 private static String CLASSDRIVRE = "com.mysql.jdbc.Driver";

 private static String USERNAME = "root";

 private static String PASSWORD = "root";

步骤一：加载 JDBC 驱动程序 

Class.forName(CLASSDRIVRE);

步骤二：提供连接 URL

/private static String URL = "jdbc:mysql://localhost:3306/mydb"; mytb为你要访问的数据库
步骤三：建立一个数据库的连接 

  Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
步骤四：创建一个 statement

Statement stat = conn.createStatement();
步骤五：创建 SQL 语句

String sqlInsert = "insert into user values(1002,'云松2','123','女',18)"; user 为你要操作的表
步骤六：执行 SQL 语句

statement.executeUpdate(sqlInsert);
步骤七：关闭 JDBC 对象

conn.close();
statement.close();*/

增删改直接处理，sql语句一次性写全了（用Statement）

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class JDBCDemo {


	/**
	 * @param args
	 */
	private static String URL = "jdbc:mysql://localhost:3306/mydb";
	private static String USERNAME = "root";
	private static String PASSWORD = "1234";
	private static String CLASSDRIVER = "com.mysql.jdbc.Driver";
	public static void main(String[] args) {
//		try {
//			insertData();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		
//		try {
//			insertAllData();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		
//		try {
//			deleteData();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		
//		try {
//			updateData();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		
//		try {
//			findUserById(2);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		
		try {
			List
    
    
     
      users = findAllUser();
			for (User user : users) {
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	//查找所有的用户信息
	private static List
     
     
      
       findAllUser() throws Exception {
		List
      
      
       
        users = new ArrayList<>();
		User user = null;
		Class.forName(CLASSDRIVER);
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		Statement statement = conn.createStatement();
		String sql = "select * from user";
		ResultSet resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			user = new User();
			user.setId(resultSet.getInt(1));
			user.setName(resultSet.getString(2));
			user.setAge(resultSet.getInt(3));
			user.setSex(resultSet.getString(4));
	        users.add(user);
		}
		conn.close();
		statement.close();
		resultSet.close();
		return users;
	}
	//根据id查找用户信息
	private static void findUserById(int i) throws Exception {
		Class.forName(CLASSDRIVER);
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		Statement statement = conn.createStatement();
		String sql = "select *from user where id = "+ i;
		ResultSet resultSet = statement.executeQuery(sql);
		User user = null;
		if (resultSet.next()) {
			user = new User();
			user.setId(resultSet.getInt(1));
			user.setName(resultSet.getString(2));
			user.setAge(resultSet.getInt(3));
			user.setSex(resultSet.getString(4));
		}
		conn.close();
		resultSet.close();
		statement.close();
		System.out.println(user);
	}
	//更新数据
	private static void updateData() throws Exception {
		Class.forName(CLASSDRIVER);
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		Statement statement = conn.createStatement();
		String sql = "update user set name = '***'where id = 4";
		statement.executeUpdate(sql);
		conn.close();
		statement.close();
		
	}
	//删除数据
	private static void deleteData() throws Exception {
		Class.forName(CLASSDRIVER);
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		Statement statement = conn.createStatement();
		String sql = "delete from user where id = 1";
		statement.executeUpdate(sql);
		conn.close();
		statement.close();
	}


	//批量添加数据
	private static void insertAllData() throws Exception {
		Class.forName(CLASSDRIVER);
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		Statement statement = conn.createStatement();
		statement.addBatch("insert into user values(2,'林青霞',25,'女')");
		statement.addBatch("insert into user values(3,'令狐冲',26,'男')");
		statement.addBatch("insert into user values(4,'赵敏',25,'女')");
		statement.addBatch("insert into user values(5,'周芷若',22,'女')");
		statement.addBatch("insert into user values(6,'小昭',23,'女')");
		statement.executeBatch();
		conn.close();
		statement.close();
	}




	//添加数据
	private static void insertData() throws Exception {
		Class.forName(CLASSDRIVER);
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		Statement statement =conn.createStatement();
		String sql = "insert into user values(1,'风清扬',45,'男')";
		statement.executeUpdate(sql);
		conn.close();
		statement.close();
		
	}


}
      
      
     
     
    
    

增删改直接处理，sql语句一开始没有写全(用PreparedStatement)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class JDBCTest {
	static String DRIVER = "com.mysql.jdbc.Driver";
	static String USERNAME = "root";
	static String PASSWORD = "1234";
	static String URL = "jdbc:mysql://localhost:3306/mydb";
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Man man = new Man();
//		man.setId(1);
//		man.setName("小明");
//		man.setShows("你是个大傻叉");
//		
//		man.setId(2);
//		man.setName("小红");
//		man.setShows("你才是个大傻叉");
//		
//		man.setId(3);
//		man.setName("小花");
//		man.setShows("你们是个大傻叉");
		
		man.setId(6);
		man.setName("lily");
		man.setShows("fafa");
//		try {
//			insertMan(man);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		 8I9JUJKI,OITY6YNGN
//		try {
//			updataData(1);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		
//		try {
//			deleteData(2);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		
//		try {
//			findManById(3);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		try {
			List
   
   
    
     mans = findAllMan();
			for (Man man2 : mans) {
				System.out.println(man2);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//查询所有的用户信息
	private static List
    
    
     
      findAllMan() throws Exception {
		List
     
     
      
       mans = new ArrayList<>();
		Man man  = null;
		Class.forName(DRIVER);
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		String sql = "select *from man";
		PreparedStatement pStatement = conn.prepareStatement(sql);
		ResultSet resultSet = pStatement.executeQuery();
		while (resultSet.next()) {
			man = new Man();
			man.setId(resultSet.getInt(1));
			man.setName(resultSet.getString(2));
			man.setShows(resultSet.getString(3));
			mans.add(man);
		}
		pStatement.close();
		conn.close();
		return mans;
	}

	//查询一个用户信息
	private static void findManById(int i) throws Exception {
		Man man = null;
		Class.forName(DRIVER);
		Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		String sql = "select *from man where id = ?";
		PreparedStatement pStatement = connection.prepareStatement(sql);
		pStatement.setInt(1, i);
		ResultSet resultSet = pStatement.executeQuery();
		if (resultSet.next()) {
			man = new Man();
			man.setId(resultSet.getInt(1));
			man.setName(resultSet.getString(2));
			man.setShows(resultSet.getString(3));
		}
		System.out.println(man);
		pStatement.close();
		connection.close();
	}

	//删除用户信息
	private static void deleteData(int index) throws Exception {
		Class.forName(DRIVER);
		Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		String sql = "delete from man where id = ?"; 
		PreparedStatement pStatement = connection.prepareStatement(sql);
		pStatement.setInt(1, index);
		pStatement.executeUpdate();
	}
	//更新用户信息
	private static void updataData(int index) throws Exception {
		Class.forName(DRIVER);
		Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		String sql = "update man set name = 'hiro',shows  = ? where id  = ?";
		PreparedStatement pStatement = connection.prepareStatement(sql);
		pStatement.setString(1, "hello test");
		pStatement.setInt(2, index)	;
		pStatement.executeUpdate();
		pStatement.close();
		connection.close();
	}
	//插入用户信息
	private static void insertMan(Man man) throws Exception {
		Class.forName(DRIVER);
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		String sql = "insert into man values(?,?,?)";
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.setInt(1, man.getId());
		preparedStatement.setString(2, man.getName());
		preparedStatement.setString(3, man.getShows());
		preparedStatement.executeUpdate();
		conn.close();
		preparedStatement.close();
		
		/*
		 * 不插入全部数据需在sql语句中指定属性
		 * String sql = "insert into man (id,shows) values(?,?)";
		  PreparedStatement preparedStatement = conn.prepareStatement(sql);
		  preparedStatement.setInt(1, man.getId());
		  preparedStatement.setString(2, man.getShows());
		  preparedStatement.executeUpdate();
		  conn.close();
		  preparedStatement.close();*/
		
		
		
	}

}