2021-04-16 Java 链接SqlServer数据库

 DemoSqlServerApplication.java

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 java.util.Scanner;
import java.util.logging.Logger;

public class DemoSqlServerApplication {

	private static final Logger log;

    static {
        System.setProperty("java.util.logging.SimpleFormatter.format", "[%4$-7s] %5$s %n");
        log =Logger.getLogger(DemoPostgresApplication.class.getName());
    }
    
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		log.info("Loading application properties");
		Properties properties = new Properties();
        properties.load(DemoSqlServerApplication.class.getClassLoader().getResourceAsStream("sqlserver.properties"));
        
        log.info("Connecting to the database");
        Connection connection = DriverManager.getConnection(properties.getProperty("jdbc.url"), properties.getProperty("jdbc.username"),properties.getProperty("jdbc.password"));
        log.info("Database connection test: " + connection.getCatalog());
        
        log.info("Create database schema");
        Scanner scanner = new Scanner(DemoPostgresApplication.class.getClassLoader().getResourceAsStream("./SQL/SqlServer.sql"));
        Statement statement = connection.createStatement();
        while (scanner.hasNextLine()) {
            statement.execute(scanner.nextLine());
        }
               
        Todo todo = new Todo(1L, "configuration", "congratulations, you have set up JDBC correctly!", true);
        insertData(todo, connection);
        todo = readData(connection);
        todo.setDetails("congratulations, you have updated data!");
        updateData(todo, connection);
        deleteData(todo, connection);
        
        log.info("Closing database connection");
        connection.close();
        
        System.out.println("####################################################################");
        System.out.println("SqlServer DB link CRUD successful!");
        System.out.println("####################################################################");
	}

	private static void deleteData(Todo todo, Connection connection) throws SQLException {
		// TODO Auto-generated method stub
	    log.info("Delete data");
	    PreparedStatement deleteStatement = connection.prepareStatement("DELETE FROM todo WHERE id = ?;");
	    deleteStatement.setLong(1, todo.getId());
	    deleteStatement.executeUpdate();
	    readData(connection);	
	}

	private static void updateData(Todo todo, Connection connection) throws SQLException {
		// TODO Auto-generated method stub
	    log.info("Update data");
	    PreparedStatement updateStatement = connection
	            .prepareStatement("UPDATE todo SET description = ?, details = ?, done = ? WHERE id = ?;");

	    updateStatement.setString(1, todo.getDescription());
	    updateStatement.setString(2, todo.getDetails());
	    updateStatement.setBoolean(3, todo.isDone());
	    updateStatement.setLong(4, todo.getId());
	    updateStatement.executeUpdate();
	    readData(connection);
	}

	private static Todo readData(Connection connection) throws SQLException {
		// TODO Auto-generated method stub
	    log.info("Read data");
	    PreparedStatement readStatement = connection.prepareStatement("SELECT * FROM todo;");
	    ResultSet resultSet = readStatement.executeQuery();
	    if (!resultSet.next()) {
	        log.info("There is no data in the database!");
	        return null;
	    }
	    Todo todo = new Todo();
	    todo.setId(resultSet.getLong("id"));
	    todo.setDescription(resultSet.getString("description"));
	    todo.setDetails(resultSet.getString("details"));
	    todo.setDone(resultSet.getBoolean("done"));
	    log.info("Data read from the database: " + todo.toString());
	    return todo;
	}

	private static void insertData(Todo todo, Connection connection) throws SQLException {
		// TODO Auto-generated method stub
	    log.info("Insert data");
	    PreparedStatement insertStatement = connection
	            .prepareStatement("INSERT INTO todo (id, description, details, done) VALUES (?, ?, ?, ?);");

	    insertStatement.setLong(1, todo.getId());
	    insertStatement.setString(2, todo.getDescription());
	    insertStatement.setString(3, todo.getDetails());
	    insertStatement.setBoolean(4, todo.isDone());
	    insertStatement.executeUpdate();
	}

}

Todo.java

public class Todo {
	private Long id;
	private String description;
	private String details;
	private boolean done;
	
	public Todo() {
	}

	public Todo(Long id, String description, String details, boolean done) {
		this.id = id;
		this.description = description;
		this.details = details;
		this.done = done;
	}
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getDetails() {
		return details;
	}
	public void setDetails(String details) {
		this.details = details;
	}
	public boolean isDone() {
		return done;
	}
	public void setDone(boolean done) {
		this.done = done;
	}
	
	@Override
	public String toString() {
		return "Todo{" + "id=" + id + ", description='" + description + '\'' + ", details='" + details + '\''
				+ ", done=" + done + '}';
	}
}

sqlserver.properties

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=xxxxxxx
jdbc.username=xxxxxxx
jdbc.password=xxxxxxx
initialSize=0
maxActive=20 
maxIdle=20
minIdle=1
maxWait=60000

SqlServer.sql

DROP TABLE IF EXISTS [dbo].[todo];
CREATE TABLE [dbo].[todo] ([id] int NOT NULL, [description] [varchar] (255), [details] [varchar](4096), [done] [bit] NOT NULL DEFAULT '0', primary key (id));