Java:JDBC(Mysql)

                                                 JDBC(Mysql)

目录

                                                 JDBC(Mysql)

一、资料地址

二、项目目录结构

三、类：CONJDBCMYSQL

 四、配置文件：jdbc.properties

---

一、资料地址

1.mysql-connector-java-5.1.46.jar下载地址:

https://share.weiyun.com/5nmRMLi

二、项目目录结构

三、类：CONJDBCMYSQL

package com.demo.jdbcmysql;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.junit.jupiter.api.Test;

import com.mysql.jdbc.Driver;

class CONJDBCMYSQL {

	private ResultSet resultSet;
	private String sqlquery;
	private String sqlinsert;
	private String sqldelete;
	private String sqlchange;
	private Statement statement;
	private Connection connection;
	private PreparedStatement prepareStatement;

	private static String driver = null;
	private static String url = null;
	private static String user = null;
	private static String password = null;

	static {
		//加载配置文件jdbc.properties
		Properties jdbcproperties = new Properties();
		try {
			jdbcproperties.load(new FileInputStream("jdbc.properties"));
			driver = jdbcproperties.getProperty("driver");
			url = jdbcproperties.getProperty("url");
			user = jdbcproperties.getProperty("user");
			password = jdbcproperties.getProperty("password");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
	
	@Test
	void test() {
		
		try {
			//注册Driver (Mysql)
			//会运行两次driver
			//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			//直接类加载只运行一次
			Class.forName(driver);
			
			//以下数据最好使用Properties配置文件存储信息
			//数据库表url
			 //url = "jdbc:mysql://localhost:3306/info";
			//登录mysql服务端用户名:root
			// user = "root";
			//登录mysql服务端密码:123
			// password = "123";
			
			connection = DriverManager.getConnection(url, user, password);
			statement = connection.createStatement();
			
			//重点:建议使用prepareStatement来防止sql注入!!!!!!而不是用createStatement()
			sqlquery = "select id from classmate where age = ? and name = ?";
			prepareStatement = connection.prepareStatement(sqlquery);
			prepareStatement.setString(1, "20");
			prepareStatement.setString(2, "li");
			prepareStatement.executeQuery();
			
			//查询
			sqlquery = "select * from classmate";
			resultSet = statement.executeQuery(sqlquery);
			
			while(resultSet.next()) {
				System.out.println(resultSet.getInt(1)+","+resultSet.getString(2)+","+resultSet.getString(3));
			}
			System.out.println("-----first query-------!");
			
			
			//插入
			sqlinsert = "insert into classmate (age,name) values (100,'mmd')";
			statement.execute(sqlinsert);
			//查询
			sqlquery = "select * from classmate";
			resultSet = statement.executeQuery(sqlquery);
			
			//删除
			sqldelete = "delete from classmate where name='mmd'";
			statement.execute(sqldelete);
			//查询
			sqlquery = "select * from classmate";
			resultSet = statement.executeQuery(sqlquery);
			
			
			//改,UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
			sqlchange = "update classmate set name='ym' where name='ryt'";
			statement.execute(sqlchange);
			//查询
			sqlquery = "select * from classmate";
			resultSet = statement.executeQuery(sqlquery);
			
			while(resultSet.next()) {
				System.out.println(resultSet.getInt(1)+","+resultSet.getString(2)+","+resultSet.getString(3));
			}
			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//断开链接，释放资源
			try {
				if(resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if(statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if(connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		
	}

}

 四、配置文件：jdbc.properties

driver:com.mysql.jdbc.Driver
url:jdbc:mysql://localhost:3306/info
user:root
password:123