sqlcript Java_【Java】连接数据库SQLServer

工具： eclipse

Microsoft SQL Server

SQL Server连接驱动：mssql-jdbc-6.4.0.jre8.jar

SQL script代码

CREATE DATABASE test

USE test

go

CREATE TABLE a

(

a1 nchar(3), --String NString

a2 nvarchar(20), --String NString

a3 int, --int int

a4 float, --double double

PRIMARY KEY (a1)

);

SELECT *

FROM a

ORDER BY 1;

INSERT INTO a (a1, a2, a3, a4)

VALUES ();

步骤：

将SQL打开，输入脚本script，运行

打开eclipse，新建JAVA项目

鼠标指向自己新建的项目，右击，建立文件夹

把mssql复制进文件夹内

右击mssql，点击build path -〉 add to build path

创建包com.jdbc，把两个JAVA文件复制进包中(JAVA database connectivity)

右击InsertSample.java，选properties

Text file encoding 选other UTF-8采用Unicode字符串

Java 连接

所需的jar包下载

百度网盘 提取码: dm6m

使用方法：

1. 在工程目录中创建lib文件夹，将下载好的JDBC(jar包)放到该文件夹下。

2. 右键工程名，选择 Buiding Path | Configure Buiding Path ，在java build path中的Libraries分页中选择Add JARs...，选择刚才添加的JDBC(jar包)。

ConnectionProperty代码

package com.jdbc;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

public class ConnectionProperty

{

//Class.forName(...); //这句话过时了 完全不需要

//DriverManager.getConnection(...) //过时了 JAVA1.0中用

public static SQLServerDataSource getDateSource()

{

SQLServerDataSource ds = new SQLServerDataSource();

ds.setServerName("10.177.7.47"); //本机IPv4号

ds.setPortNumber(1433); //端口号

ds.setUser("sa"); //用户名

ds.setPassword("123"); //密码

ds.setDatabaseName("test"); //数据库名称

ds.setSendTimeAsDatetime(false);

return ds;

}

}

InsertSample代码

package com.jdbc;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

import javax.sql.DataSource;

public class InsertSample {

public static void main(String[] args) {

//insertA();

selectA();

}

public static void insertA() {

DataSource ds = ConnectionProperty.getDataSource();

String sql = "INSERT INTO a (a1, a2, a3, a4) "

+ "VALUES (?, ?, ?, ?)";

try (Scanner scanner = new Scanner(System.in);

Connection con = ds.getConnection();

PreparedStatement pstmt = con.prepareStatement(sql)) {

System.out.print("a1=");

String a1 = scanner.nextLine();

System.out.print("a2=");

String a2 = scanner.nextLine();

System.out.print("a3=");

int a3 = scanner.nextInt();

System.out.print("a4=");

double a4 = scanner.nextDouble();

pstmt.setNString(1, a1);

pstmt.setNString(2, a2);

pstmt.setInt(3, a3);

pstmt.setDouble(4, a4);

int rowCount = pstmt.executeUpdate();

System.out.println("插入" + rowCount + "行");

// System.out.println("a1=" + a1);

// System.out.println("a2=" + a2);

// System.out.println("a3=" + a3);

// System.out.println("a4=" + a4);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void selectA() {

DataSource ds = ConnectionProperty.getDataSource();

String sql = "SELECT a1, a2, a3, a4 "

+ "FROM a "

+ "WHERE a3 > ? "

+ "ORDER BY a1";

try (Scanner scanner = new Scanner(System.in);

Connection con = ds.getConnection();

PreparedStatement pstmt = con.prepareStatement(sql)) {

System.out.print("a3=");

int a3 = scanner.nextInt();

pstmt.setInt(1, a3);

ResultSet rs = pstmt.executeQuery();

while (rs.next()) {

System.out.print(rs.getNString("a1") + "\t");

System.out.print(rs.getNString("a2") + "\t");

System.out.print(rs.getInt("a3") + "\t");

System.out.println(rs.getDouble("a4"));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

提示

可保存这此Java项目，下次连接可直接导入，只需修改部分信息。