java连数据库_java连接数据库

访问https://docs.microsoft.com/zh-cn/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15获取sqlserver驱动，访问https://downloads.mysql.com/archives/c-j/获取mysql驱动。

下载mysql驱动

解压文件夹找到jar文件导入IDEA(路径选择:文件->项目结构->模块->依赖项，点击+导入jar包)，下面我用代码测试数据库。sqlserver 数据库名:CRM，表名userInfo，列名userNo;userName;userPswd;Age;Sex;Job;Address;

package JDBC测试;

import java.sql.*;

public class JDBC {

public static void main(String[] args) {//这里是sqlserver测试程序

try {

/*加载数据库驱动*/

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

System.out.println("数据库驱动加载成功");

}catch (ClassNotFoundException e){

e.printStackTrace();

}

try {

/*getConnection()接口定义:(它可以连接数据库)

getConnection("数据库连接地址;Database=数据库名称","用户名","密码")*/

Connection connection = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1;DatabaseName=CRM","root","admin");

System.out.println("数据库连接成功");

showDatabase(connection);//显示数据表

//执行sql增删改查

/*String sql="delete from userInfo where userName='张怡'";

if(changeDatabase(connection,sql)>0){

System.out.println("删除成功");

}else{

System.out.println("删除失败");

}*/

/*String sql="update userInfo set userPswd='5433232' where userName='梁家辉'";

if(changeDatabase(connection,sql)>0){

System.out.println("更新成功");

}else{

System.out.println("更新失败");

}*/

String sql="INSERT INTO userInfo VALUES (1008,'张掖','1123','43','男','辅导员','入门大学')";//sql语句

if(changeDatabase(connection,sql)>0){

System.out.println("添加成功");

}else{

System.out.println("添加失败");

}

showDatabase(connection);

connection.close();

} catch (SQLException throwables) {

System.out.println("数据库连接失败");

throwables.printStackTrace();

}

}

private static int changeDatabase(Connection connection, String sql) throws SQLException {

Statement statement=connection.createStatement();//创建数据库连接

int rs=statement.executeUpdate(sql);//执行sql语句

statement.close();//关闭数据库连接

return rs;

}

private static void showDatabase(Connection connection) throws SQLException {

Statement statement= connection.createStatement();//创建数据库连接

String sql="SELECT * FROM userInfo";//选择表

System.out.println("职工信息表");

System.out.println("账号\t\t姓名\t密码\t年龄\t\t\t性别\t\t\t\t工作\t地址");

ResultSet rs = statement.executeQuery(sql);//执行sql

while(rs.next()){

String sq=rs.getString("userNo");

sq+="\t"+rs.getString("userName");

sq+="\t"+rs.getString("userPswd");

sq+="\t"+rs.getString("Age");

sq+="\t"+rs.getString("Sex");

sq+="\t"+rs.getString("Job");

sq+="\t"+rs.getString("Address");

System.out.println(sq);

}

rs.close();

}

}

mysql测试 数据库名test，表名mytable列名id;name

package JDBC测试;

import java.sql.*;

import java.util.Properties;

public class JDBCTest {

public static Connection testDriver() throws SQLException{//mysql测试

Driver driver=new com.mysql.cj.jdbc.Driver();//加载mysql数据库驱动

/*String url=""jdbc:数据库名称(mysql或sqlserver等)://主机ip或本机/数据库名字?调整时区*/

String url="jdbc:mysql://localhost:3306/test?useSSL=true&characterEncoding=utf-8&serverTimezone=GMT";

Properties info=new Properties();

/*设置数据库账号密码*/

info.put("user","root");

info.put("password","admin");

Connection connection=driver.connect(url, info);//创建连接

return connection;

}

public static void getData(){

try {

Connection connection=testDriver();

Statement statement=connection.createStatement();

String sql="SELECT * FROM mytable";

ResultSet rs = statement.executeQuery(sql);

while(rs.next()){//三种方法测试查询数据库

/*System.out.println(rs.getString(1));//通过列的序号获取列值

System.out.println(rs.getInt(1));//通过列的序号获取列值

System.out.println(rs.getString("name"));//通过列名获取列值*/

String sq=rs.getString("id");

sq+="\t"+rs.getString("name");

System.out.println(sq);

}

rs.close();

connection.close();

} catch (SQLException throwables) {

throwables.printStackTrace();

}

}

public static void testStatement(){

try {

Connection conn=testDriver();//增删改查

String sql=" INSERT INTO mytable VALUES(1221,'wdscx');";//增

//String sql="DELETE FROM mytable WHERE id=12";//删

//String sql=" UPDATE mytable SET id=1";//改

Statement statement=conn.createStatement();

statement.executeUpdate(sql);

statement.close();

conn.close();

} catch (SQLException throwables) {

throwables.printStackTrace();

}

}

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

testStatement();

getData();

}

}