java9怎么连接数据库_Java连接数据库

JDBC

数据库驱动和JDBC

(1)数据库驱动是连接数据库所需，由厂商提供，程序和数据库驱动打交道

(2)jdbc是一种规范，主要是为了简化开发人员对数据的统一的操作而提供的一个规范

第一个JDBC程序

package com.ch.jdbc;

import java.sql.*;

public class MyFirstJdbc {

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

//1、加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//2、准备连接信息：url，username，password

String url = "jdbc:mysql://localhost:3306/mysqlstudy?serverTimezone=GMT&characterEncoding=utf8&useSSL=true";

String username = "root";

String password = "root";

//3、连接数据库

Connection conn = DriverManager.getConnection(url, username, password);

//4、获取执行SQL语句的对象

Statement statement = conn.createStatement();

//5、执行SQL语句

ResultSet query = statement.executeQuery("select * from student");

while (query.next()){

int id = query.getInt("id");

int score = query.getInt("score");

String name = query.getString("name");

System.out.println(id + "------"+ name + "------"+score);

}

//6、释放资源

query.close();

statement.close();

conn.close();

}

}

URL

String url = "jdbc:mysql://localhost:3306/mysqlstudy?serverTimezone=GMT&characterEncoding=utf8&useSSL=true";

//mysql -- 3306

//jdbc:mysql://localhost:3306/数据库?参数1&参数2&参数3

//oracle -- 1521

//jdbc:oracle:thin@localhost:1521:sid

//SQLServer -- 1433

//jdbc:sqlserver://localhost:1433;DatabaseName=test

DriverManager

Connection conn = DriverManager.getConnection(url, username, password);

//conn代表数据库对象

//数据库可以设置提交

//设置回滚

//设置自动提交

conn.commit();

conn.rollback();

conn.setAutoCommit();

Statement

Statement statement = conn.createStatement();

//statement用来执行SQL的对象，prepareStatement也是用来执行SQL对象的

statement.execute(); //执行任何SQL

statement.executeQuery(); //查询操作，返回ResultSet

statement.executeUpdate(); //更新、插入、删除。返回受影响行

statement.executeBatch(); //执行多个SQL

ResultSet

//封装了所有的查询结果

query.getObject(); //不知道数据类型的情况下使用

query.getString();

query.getInt();

query.getDate();

……

简单封装代码实现

jdbc.properties

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/mysqlstudy?serverTimezone=GMT&characterEncoding=utf8&useSSL=true

username=root

password=root

JdbcUtil

package com.ch.util;

import java.io.InputStream;

import java.sql.*;

import java.util.Properties;

public class JdbcUtil {

private static String driver;

private static String url;

private static String username;

private static String password;

//1、获取配置文件的信息并加载驱动

static {

try {

//读取配置文件的内容

InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");

Properties properties = new Properties();

properties.load(in);

//获取数据库相关信息

driver = properties.getProperty("driver");

url = properties.getProperty("url");

username = properties.getProperty("username");

password = properties.getProperty("password");

//加载驱动

Class.forName(driver);

} catch (Exception e) {

}

}

//2、获取连接

public static Connection getConnection(){

Connection conn = null;

try {

conn = DriverManager.getConnection(url,username,password);

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

//3、释放资源

public static void close(Connection conn, Statement st, ResultSet rs){

if (rs != null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (st != null){

try {

st.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (conn != null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

JdbcTest

package com.ch.jdbc;

import com.ch.util.JdbcUtil;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

* 增删查改测试

*/

public class JdbcTest {

/**

* 增删改操作

* @param sql 填写需要执行的sql语句

* @param message 操作是增加还是删除或者修改

*/

public static void update(String sql,String message){

//获取连接

Connection conn = JdbcUtil.getConnection();

//获取执行SQL的对象

Statement st = null;

int result = 0;

try {

st = conn.createStatement();

//执行SQL

result = st.executeUpdate(sql);

} catch (SQLException e) {

e.printStackTrace();

}

if (result > 0){

System.out.println(message+"成功");

}else{

System.out.println(message+"失败");

}

//执行完释放资源

JdbcUtil.close(conn,st,null);

}

/**

* 查询操作

*/

public static void query(){

//获取连接

Connection conn = JdbcUtil.getConnection();

//获取执行SQL的对象

Statement st = null;

ResultSet rs = null;

try {

st = conn.createStatement();

//执行SQL语句

String sql = "select * from student";

rs = st.executeQuery(sql);

while (rs.next()){

int id = rs.getInt("id");

int score = rs.getInt("score");

String name = rs.getString("name");

System.out.println(id + "------"+ name + "------"+score);

}

} catch (SQLException e) {

e.printStackTrace();

}

//执行完释放资源

JdbcUtil.close(conn,st,null);

}

//测试

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

//添加

String sql = "insert into student(id,score,`name`) values(10,72,'小四');";

String message = "插入";

update(sql,message);

//修改

String sql1 = "update student set `name`='张华' where id = 10";

String message1 = "修改";

update(sql1,message1);

//删除

String sql2 = "delete from student where id = 10";

String message2 = "删除";

update(sql2,message2);

//查询

query();

}

}

SQL注入问题

SQL注入就是sql存在漏洞，SQL语句被拼接

public static void doLogin(String uname){

//获取连接

Connection conn = JdbcUtil.getConnection();

//获取执行SQL的对象

Statement st = null;

ResultSet rs = null;

try {

st = conn.createStatement();

//执行SQL语句

String sql = "select * from student where `name`='"+uname+"'";

rs = st.executeQuery(sql);

while (rs.next()){

int id = rs.getInt("id");

int score = rs.getInt("score");

String name = rs.getString("name");

System.out.println(id + "------"+ name + "------"+score);

}

} catch (SQLException e) {

e.printStackTrace();

}

//执行完释放资源

JdbcUtil.close(conn,st,null);

}

防SQL注入改进

使用PrepareStatement 可以防止SQL注入，效率更高(预编译)

package com.ch.jdbc;

import com.ch.util.JdbcUtil;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

/**

* 防SQL注入问题的一种改进

*/

public class PreJdbcTest {

public static void main(String[] args) {

Connection conn = JdbcUtil.getConnection();

String sql = "select * from student where `name`= ?";

PreparedStatement pst = null;

ResultSet rs = null;

try {

pst = conn.prepareStatement(sql);

pst.setString(1,"张三");

rs = pst.executeQuery();

while (rs.next()){

int id = rs.getInt("id");

int score = rs.getInt("score");

String name = rs.getString("name");

System.out.println(id + "------"+ name + "------"+score);

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

标签：Java,String,rs,数据库,SQL,sql,连接,conn,name

来源： https://www.cnblogs.com/IT_CH/p/13458603.html