java学习---mysql数据库编程

1. //步骤1：新建一个数据库，数据库名为interactive_community，字段：id,cNickName,cNickName,cPassword.（具体代码中显示）
2. //步骤2：先下载mysql驱动，建立好工程，引入mysql连接驱动 

     3.  //步骤3：代码如下

  
//主函数

package com.dragon.mysql;

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

public class Main {

	//数据库名
	static String dbname = "interactive_community";
	// MySQL配置时的用户名
	static String user = "root";
	// MySQL配置时的密码
	static String password = "dragon";
	
	// URL指向要访问的数据库名interactive_community
	static String url = "jdbc:mysql://127.0.0.1:3306/"+dbname;
	
	// 要执行的SQL语句 使用占位符
	static String query_sql = "select * from tb_user";
	static String update_sql = "update tb_user set cNickName=? where id=?";
	static String delete_sql = "delete from tb_user where id=?";
	static String insert_sql = "insert into tb_user(cUserName,cPassword,cNickName) values(?,?,?)";
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {

			//1.新建数据库对象
			MysqlAdapter mysqlAdapter = new MysqlAdapter();
			Connection connection =  mysqlAdapter.mysqlConnect(url, user, password);

			//Query(mysqlAdapter,connection);//查询
			
			//Update(mysqlAdapter,connection);//更新
			
			//Delete(mysqlAdapter, connection);//删除
			
			Insert(mysqlAdapter, connection);//插入

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 查询数据库
	 * @param mysqlAdapter
	 * @param connection
	 */
	public static void Query(MysqlAdapter mysqlAdapter,Connection connection){
		//2.执行SQL语句并返回结果集
		ResultSet rs = mysqlAdapter.mysql_query(connection,query_sql);
		System.out.println(" 用户名" + "\t" + " 昵称");
		try {
			while (rs.next()) {
				//输出结果
				System.out.println(rs.getString("cNickName") + "\t" + rs.getString("cUserName"));
			}
		    //3.关闭结果集
			rs.close();
			
			//4.关闭连接
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 更新
	 * @param mysqlAdapter
	 * @param connection
	 */
	public static void Update(MysqlAdapter mysqlAdapter,Connection connection){
		int row = 0;
		try {
			row = mysqlAdapter.mysql_update(connection, update_sql);
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if(row>0){
			System.out.println("更新成功");
		}else{
			System.out.println("更新失敗");
		}
	}
	
	/**
	 * 删除
	 * @param mysqlAdapter
	 * @param connection
	 */
	public static void Delete(MysqlAdapter mysqlAdapter,Connection connection){
		boolean  result =  false;
		
		try {
			result =  mysqlAdapter.mysql_delete(connection, delete_sql, 15);
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if(result){
			System.out.println("删除成功");
		}else{
			System.out.println("删除失败");
		}
	}
	
	/**
	 * 插入
	 * @param mysqlAdapter
	 * @param connection
	 */
	public static void Insert(MysqlAdapter mysqlAdapter,Connection connection){
		int rows = mysqlAdapter.mysql_insert(connection, insert_sql);
		
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if(rows>0){
			System.out.println("插入成功");
		}else{
			System.out.println("插入失败");
		}
	}

}

//mysql数据库操作：增、删、查、改

package com.dragon.mysql;

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

import com.mysql.jdbc.PreparedStatement;

public class MysqlAdapter {
	
	/**
	 * 数据库连接
	 * @param url
	 * @param user
	 * @param password
	 * @return
	 */
	public Connection mysqlConnect(String url,String user,String password){
		
		Connection connection = null;
		// 加载驱动程序
		try {
			// 加载驱动程序
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(url, user,password);//连接数据库
			if (!connection.isClosed()){
				System.out.println("成功连接数据库");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * 查詢数据
	 * @param connection
	 * @param sql
	 * @return
	 */
	public ResultSet mysql_query(Connection connection,String sql){
		ResultSet rs = null;
		try {
			// statement用来执行SQL语句
			Statement statement = connection.createStatement();
			// 执行SQL语句并返回结果集
		    rs = statement.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return rs;
	}
	
	/**
	 * 删除数据
	 * @param connection
	 * @param sql
	 * @param id
	 * @return
	 */
	public boolean mysql_delete(Connection connection,String sql,int id){
		boolean result = false;
		PreparedStatement sta;
		try {
			sta = (PreparedStatement) connection.prepareStatement(sql);
			sta.setInt(1, id);//第一個參數
			
			int rows = sta.executeUpdate();
			
			if(rows > 0){
				result = true;
			}else{
				result = false;
			}
			
			sta.close();
		} catch (Exception e) {
			result = false;
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * 插入数据
	 * @param connection
	 * @param sql
	 * @return
	 */
	public int mysql_insert(Connection connection,String sql){
		int rows = 0;
		PreparedStatement sta;
		try {
			sta = (PreparedStatement) connection.prepareStatement(sql);
			sta.setString(1, "YY");//第一個參數
			sta.setString(2, "e10adc3949ba59abbe56e057f20f883e");//第二個參數
			sta.setString(3, "IC_YY");//第三個參數
			
			rows = sta.executeUpdate();
			
		} catch (Exception e) {
			rows = 0;
			e.printStackTrace();
		}
		return rows;
	}

	/**
	 * 更新數據
	 * @param connection
	 * @param sql
	 * @return
	 */
	public int mysql_update(Connection connection,String sql){
		int rows = 0;
		try {
			PreparedStatement sta = (PreparedStatement) connection.prepareStatement(sql);
			sta.setString(1, "dragon");//第一個參數
			sta.setInt(2, 18);//第二個參數
			
			rows = sta.executeUpdate();//執行
			
	        sta.close();
	        
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return rows;
	}
	
}